删除列中的非数字字符（字符变化），postgresql (9.3.5)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28606423/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
remove non-numeric characters in a column (character varying), postgresql (9.3.5)
提问by mihk3l
I need to remove non-numeric characters in a column (character varying) and keep numeric values in postgresql 9.3.5.
我需要删除列中的非数字字符（字符变化）并在 postgresql 9.3.5 中保留数字值。
Examples:
例子：
1) "ggg" => ""
2) "3,0 kg" => "3,0"
3) "15 kg." => "15"
4) ...
There are a few problems, some values are like:
有一些问题，一些值是这样的：
1) "2x3,25"
2) "96+109"
3) ...
These need to remain as is (i.e when containing non-numeric characters between numeric characters - do nothing).
这些需要保持原样（即在数字字符之间包含非数字字符时 - 什么都不做）。
采纳答案by Simo Kivist?
For modifying strings in PostgreSQL take a look at The String functions and operatorssection of the documentation. Function substring(string from pattern)uses POSIX regular expressions for pattern matchingand works well for removing different characters from your string.
(Note that the VALUESclause inside the parentheses is just to provide the example material and you can replace it any SELECTstatement or table that provides the data):
要在 PostgreSQL 中修改字符串，请查看文档的字符串函数和运算符部分。函数substring(string from pattern)使用POSIX 正则表达式进行模式匹配，并且可以很好地从字符串中删除不同的字符。
（请注意，VALUES括号内的子句仅提供示例材料，您可以将其替换SELECT为提供数据的任何语句或表格）：
SELECT substring(column1 from '(([0-9]+.*)*[0-9]+)'), column1 FROM
(VALUES
('ggg'),
('3,0 kg'),
('15 kg.'),
('2x3,25'),
('96+109')
) strings
The regular expression explained in parts:
正则表达式分部分解释：
[0-9]+- string has at least one number, example:'789'[0-9]+.*- string has at least one number followed by something, example:'12smth'([0-9]+.\*)*- the string similar to the previous line zero or more times, example:'12smth22smth'(([0-9]+.\*)*[0-9]+)- the string from the previous line zero or more times and at least one number at the end, example:'12smth22smth345'
[0-9]+- 字符串至少有一个数字，例如：'789'[0-9]+.*- 字符串至少有一个数字后跟一些东西，例如：'12smth'([0-9]+.\*)*- 与前一行相似的字符串零次或多次，例如：'12smth22smth'(([0-9]+.\*)*[0-9]+)- 前一行中的字符串零次或多次，最后至少有一个数字，例如：'12smth22smth345'
回答by pensnarik
Using regexp_replaceis more simple:
使用regexp_replace更简单：
# select regexp_replace('test1234test45abc', '[^0-9]+', '', 'g');
regexp_replace
----------------
123445
(1 row)
The ^means not, so any character that is notin the range 0-9will be replaced with an empty string, ''.
的^装置not，使得任何字符不范围内0-9将与空字符串替换，''。
The 'g'is a flag that means all matches will be replaced, not just the first match.
这'g'是一个标志，表示将替换所有匹配项，而不仅仅是第一个匹配项。
