All Forums Analytics
AndreyAlex 3 posts Joined 04/16
29 Apr 2016
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.

dnoeth 4628 posts Joined 11/04
29 Apr 2016

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

 

Dieter

AndreyAlex 3 posts Joined 04/16
29 Apr 2016

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!

AndreyAlex 3 posts Joined 04/16
29 Apr 2016

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

dnoeth 4628 posts Joined 11/04
29 Apr 2016

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

 

Dieter

You must sign in to leave a comment.