extract number from string in Oracle

Question

I am trying to extract a specific text from an Outlook subject line. This is required to calculate turn around time for each order entered in SAP. I have a subject line as below

SO# 3032641559 FW: Attached new PO 4500958640- 13563 TYCO  LJ

My final output should be like this: 3032641559

I have been able to do this in MS excel with the formulas like this

=IFERROR(INT(MID([@[Normalized_Subject]],SEARCH(30,[@[Normalized_Subject]]),10)),"Not Found")

in the above formula [@[Normalized_Subject]] is the name of column in which the SO number exists. I have asked to do this in oracle but I am very new to this. Your help on this would be greatly appreciated.

Note: in the above subject line the number 30 is common in every subject line.

stackoverflow.com/questions/9755681/…

Itay
– Itay

2013-09-07 05:58:27 +00:00
Commented Sep 7, 2013 at 5:58 — Itay
– Itay, Commented Sep 7, 2013 at 5:58

Ben · Accepted Answer · 2013-09-07 08:31:43Z

2

The last parameter of REGEXP_SUBSTR() indicates the sub-expression you want to pick. In this case you can't just match 30 then some more numbers as the second set of digits might have a 30. So, it's safer to match the following, where x are more digits.

SO# 30xxxxxx

As a regular expression this becomes:

SO#\s30\d+

where \s indicates a space \d indicates a numeric character and the + that you want to match as many as there are. But, we can use the sub-expression substringing available; in order to do that you need to have sub-expressions; i.e. create groups where you want to split the string:

(SO#\s)(30\d+)

Put this in the function call and you have it:

regexp_substr(str, '(SO#\s)(30\d+)', 1, 1, 'i', 2)

SQL Fiddle

answered Sep 7, 2013 at 8:31

Ben

53.1k36 gold badges133 silver badges156 bronze badges

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

1 Comment

Gordon Linoff Over a year ago

@santhosha . . . This looks like the correct answer. You should accept it if you agree.

Collectives™ on Stack Overflow

extract number from string in Oracle

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related