Deriving value from a string in oracle

Question

I have a column called NARRATION in my table and it has values like as shown below.

NARRATION
-------------------------------------
BI DS NO 6005527182
BI DS NO. 485019
CI DS NO.6008364862(2000*1) 
CI DS NO.1905180207 10*1) EDDF34592845
CI DS NO.2105180520-(50*1) DDFF39844240
CI DS NO.2105180653 (2000*1) DFSD39844240
CI DS NO.1905180220/(500*3,20
CI DS NO.2105180166*(200*1) DSFSF39844240

I want to derive first word after the text DS NO. till the next space or any other special character like as shown below. Some times there might be space also exists after DS NO. and from the space word starts that i need to derive.

NARRATION                                 |DERIVE_NARRATION  
------------------------------------------|----------------
BI DS NO 6005527182                       |6005527182
BI DS NO. 485019                          |485019
CI DS NO.6008364862(2000*1)               |6008364862
CI DS NO.1905180207 10*1) EDDF34592845    |1905180207
CI DS NO.2105180520-(50*1) DDFF39844240   |2105180520
CI DS NO.2105180653 (2000*1) DFSD39844240 |2105180653
CI DS NO. 1905180220/(500*3,20            |1905180220
CI DS NO.2105180166*(200*1) DSFSF39844240 |2105180166

Please help me query.

Is your "word" after DO always a number?

Art
– Art

2018-05-25 13:31:40 +00:00
Commented May 25, 2018 at 13:31 — Art
– Art, Commented May 25, 2018 at 13:31

Aleksej · Accepted Answer · 2018-05-25 12:59:40Z

You can use regexp_substr to get what you need. Assuming that your "special chars" are all listed in your sample data, this should do the job

select narration,
       regexp_substr(narration, 'DS NO([ \.]*)([^- \\/\*(]*)', 1, 1, 'i', 2)
from tab

NARRATION                                 DERIVE_NARRATION
----------------------------------------- ----------------------------
BI DS NO 6005527182                       6005527182
BI DS NO. 485019                          485019
CI DS NO.6008364862(2000*1)               6008364862
CI DS NO.1905180207 10*1) EDDF34592845    1905180207
CI DS NO.2105180520-(50*1) DDFF39844240   2105180520
CI DS NO.2105180653 (2000*1) DFSD39844240 2105180653
CI DS NO. 1905180220/(500*3,20            1905180220
CI DS NO.2105180166*(200*1) DSFSF39844240 2105180166

How it works: it looks for the string "DS NO" followed by spaces or dots and then gets the following characters, going onward until it finds a "special char", listed in [^- \\/\*(]. The parameter 2 is used to get the second matching expression.

With that sample data, it seems that you need to get a number, more that everything up to a "special char"; if so, you can edit it into:

regexp_substr(narration, 'DS NO([ \.]*)([0-9]*)', 1, 1, 'i', 2)

Collectives™ on Stack Overflow

Deriving value from a string in oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related