CODEWITHSUNDEEP
sqlregexpostgresqlselect
Shawn12
Shawn12

Reputation: 41

Return numbers in string:postgresql

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

Upvotes: 3

Views: 53

Answers (2)

GMB
GMB

Reputation: 222482

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.

Demo on DB Fiddle

Upvotes: 1

Bohemian
Bohemian

Reputation: 425033

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

See live demo.

Upvotes: 0

Related Questions