How to extract only 5 digit number from a string using REGEXP sql

Question

I need to extract only 5 digit numbers from a string using REGEXP sql in postgresql or redshift DB

For example,

f 34 123 54321 123456

above string should return

54321

I am able to get only numbers from string but not only 5 digit number

select REGEXP_REPLACE('f 34 123 54321 123456', '[^0-9]') five_digit_number
--returns 3412354321123456

Answer 1

Use regexp_matches.

select regexp_matches('f 34 123 54321 123456','\y\d{5}\y','g')

Specifying 'g' flag gives you all the matches in case there is more than one 5 digit occurrence in the string.

\y specifies a word boundary.

For Redshift, you can use regexp_substr.

select REGEXP_SUBSTR('f 34 123 54321 123456', '(^|[^[:word:]]|[[:space:]])\\d{5}([^[:word:]]|[[:space:]]|$)')