CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user1463065
user1463065

Reputation: 595

Deriving value from a string in oracle

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.

Upvotes: 0

Views: 52

Answers (1)

Aleksej
Aleksej

Reputation: 22949

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)

Upvotes: 3

Related Questions