1

I have a string from database field which contains the data in below format. The UW:,Loading:, CBE: fields can be present.

ex1:

"[UW:Loading25,CBE]
[Loading: 100;120;130]
[CBE:150;170;190]"

ex2:

"[UW: CBE]
[CBE: 100;122;130]"

ex3:

"[UW:Loading25]
[Loading: 100;120;130]"

I have to separate them in different columns as mentioned below:

     UW       |    Loading     |  CBE  
--------------|----------------|------------
Loading25,CBE |  100;120;130   | 150;170;190
--------------|----------------|------------
CBE           |                |100;122;130 
--------------|----------------|------------
Loading25     |  100;120;130   |

How can I resolve this?

halfer
  • 19,824
  • 17
  • 99
  • 186
Ashutosh
  • 111
  • 14

1 Answers1

1

You can use regexp_substr together with ltrim for each search patterns : 

with t(str) as
(
 select '[UW:Loading25,CBE][Loading: 100;120;130][CBE:150;170;190]' from dual union all
 select '[UW: CBE][CBE: 100;122;130]' from dual union all
 select '[UW:Loading25][Loading: 100;120;130]' from dual  
)
select ltrim(regexp_substr(str,'UW:([^]]+)'),'UW:') as uw,
       ltrim(regexp_substr(str,'Loading:([^]]+)'),'Loading:') as loading,
       ltrim(regexp_substr(str,'CBE:([^]]+)'),'CBE:') as cbe
  from t;

UW               LOADING        CBE
-------------    -------------  ------------
Loading25,CBE    100;120;130    150;170;190
CBE                             100;122;130
Loading25        100;120;130

Repeating words such as Loading or CBE should be noticed during the match of patterns.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • Hi @Barbaros, the above solution is not working for some scenarios like :[UW: CBE] [CBE: 100;122;130]. Please can you tell whats wrong – Ashutosh May 08 '19 at 09:38
  • Hi @Ashutosh, you're right. I converted to the above one which works flawlessly. – Barbaros Özhan May 08 '19 at 12:36
  • Thanks. that worked. Can you please tell me some link to go through the Regexp so that i can determine myself. I need to few other functionalities as well. – Ashutosh May 09 '19 at 09:47
  • you're welcome @Ashutosh. [Oracle Tutorial Link](https://www.techonthenet.com/oracle/) is a nice source, where you can search from right upper part for those string operations such as `regexp_like`, `regexp_substr`,`regexp_count`..etc. – Barbaros Özhan May 09 '19 at 12:43