仅返回列中的数值 - Postgresql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21946657/
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
Return only numeric values from column - Postgresql
提问by Luigi
I have several values in my namecolumn within the contactstable similar to this one:
我name在contacts表中的列中有几个与此类似的值：
test 3100509 DEMO NPS
test 3100509 DEMO NPS
I want to return only the numeric piece of each value from name.
我只想从name.
I tried this:
我试过这个：
select substring(name FROM '^[0-9]+|.*') from contacts
select substring(name FROM '^[0-9]+|.*') from contacts
But that doesn't do it.
但这不行。
Any thoughts on how to strip all characters that are not numeric from the returned values?
关于如何从返回值中去除所有非数字字符的任何想法？
回答by Rida BENHAMMANE
Try this :
试试这个 ：
select substring(name FROM '[0-9]+') from contacts
回答by peter.petrov
select regexp_replace(name , '[^0-9]*', '', 'g') from contacts;
select regexp_replace(name , '[^0-9]*', '', 'g') from contacts;
This should do it. It will work even if you have more than one numeric sequences in the name.
这应该做。即使名称中有多个数字序列，它也能工作。
Example:
例子：
create table contacts(id int, name varchar(200));
insert into contacts(id, name) values(1, 'abc 123 cde 555 mmm 999');
select regexp_replace(name , '[^0-9]*', '', 'g') from contacts;
回答by Nouman Bhatti
If you want to extract the numeric values with decimal point than use this
如果要提取带小数点的数值，请使用此方法
select NULLIF(regexp_replace(name, '[^0-9.]*','','g'), '')::numeric from contacts
