Question

Views

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');

over 4 years ago · Santiago Trujillo

Answer question

Answer 1 · 2022-05-11T20:49:49.433Z

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.

Answer 2 · 2022-05-11T20:49:49.441Z

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