仅返回列中的数值 - Postgresql

I have several values in my name column within the contacts table similar to this one:

我在联系人表格中的名称列中有几个与此类似的值:

test 3100509 DEMO NPS

测试3100509 DEMO NPS

I want to return only the numeric piece of each value from name.

我想只返回名称中每个值的数字部分。

I tried this:

我试过这个:

select substring(name FROM '^[0-9]+|.*') from contacts

从联系人中选择子字符串(名称FROM'^ [0-9] + |。*')

But that doesn't do it.

但那不行。

Any thoughts on how to strip all characters that are not numeric from the returned values?

有关如何从返回值中删除非数字的所有字符的任何想法?

3 个解决方案

#1

Try this :

尝试这个 :

select substring(name FROM '[0-9]+') from contacts

#2

select regexp_replace(name , '[^0-9]*', '', 'g') from contacts;

从联系人中选择regexp_replace(name,'[^ 0-9] *','','g');

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;

#3

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

#1