CODEWITHSUNDEEP
regexoracle-database
santhosha
santhosha

Reputation: 629

extract number from string in Oracle

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.

Upvotes: 0

Views: 5355

Answers (1)

Ben
Ben

Reputation: 52913

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

Upvotes: 2

Related Questions