Analytics
Fan

## 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
Senior Apprentice

## 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`
Fan

## 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!

Fan

## 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]')

Senior Apprentice

## 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}')`