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

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

Vamsi Prabhala · Accepted Answer · 2017-01-06 20:20:19Z

4

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:]]|$)')

answered Jan 6, 2017 at 19:46

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

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

4 Comments

i get ERROR: 42883: function regexp_matches("unknown", "unknown") does not exist

which version of postgres are you on?

I use redshift db... REGEXP_REPLACE works but not this