Return numbers in string:postgresql

Question

I have the string: I am 10 years old with 500 friends. I want to return 10 and 500 but when I executed the query below and it returned empty:

SELECT
REGEXP_MATCHES('I have the string: I am 10 years old with 500  friends',
                '-?\\d+','g');

GMB · Accepted Answer · 2020-06-27 20:27:01Z

1

The problem is with the double anti-slash. While some databases require regex character classes to be escaped, Postgres expects just one anti-slash.

So:

select regexp_matches(
    'I have the string: I am 10 years old with 500 friends',
    '-?\d+',
    'g'
);

'-?' is not sensible for your example string. I left it as is in case you want to accomodate for possible negative numbers.

answered Jun 27, 2020 at 20:27

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bohemian · Accepted Answer · 2020-06-27 20:20:06Z

0

Replace \\d with [0-9]:

SELECT
  REGEXP_MATCHES(
    'I have the string: I am 10 years old with 500 friends',
    '(-?[0-9]+)','g'
  )

Output:

10
500

answered Jun 27, 2020 at 20:20

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges