Extract numeric values from string

Analytics

Extract numeric values from string

Hi!

I can't find solution, may be somebody can help me.

Example:

SELECT
'ABC123D666656-522815EF!@55#' AS COL1

I need to extract 666656-52281. But other string chartes can be different. Also can vary their number. I need XXXXXX-XXXXX (where X=[0-9]) from any kind of text.

I stoped on this step:

SELECT
'ABC123D666656-522815EF!@55#' AS COL1,
REGEXP_REPLACE(COL1,'[^0-9]+',' ',1,0,'i') AS COL2

P.S. Sorry for my English.

Tags (3)
4 REPLIES
Junior Contributor

Re: Extract numeric values from string

Use a REGEXP_SUBSTR to extract a group of digits [0-9]+ followed by a dash - followed by a group of digits [0-9]+

REGEXP_SUBSTR(COL1,'[0-9]+-[0-9]+') AS COL2

Re: Extract numeric values from string

Thank you! But is it posible to count numeric values in string? Because I need strong number count before and after '-'. Situation when string is:

SELECT
'ABC123D45345345666656-5228155668EF!@55#' AS COL1

I need the same result XXXXXX-XXXXX.

Thank you one more time!

Re: Extract numeric values from string

thank I find anwer!

REGEXP_SUBSTR(COL1,'[0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]')

Junior Contributor

Re: Extract numeric values from string

I didn't see that it's an exact number of digits, no need to repeat [0-9] multiple times, you can specify the number directly:

REGEXP_SUBSTR(COL1,'[0-9]{6}-[0-9]{5}')