CODEWITHSUNDEEP
sqloracle-database
Anil
Anil

Reputation: 49

Split string in to multiple columns using reqular expression in sql

Currently i have column called TEXT in my table having data with "High SMS usage - VPMN: XXXXX - SRC: XXX - SDR: XX.YY - Will Report"

By using regular expression with delimiter(-) i can able to separate string like attached screen shot Screen shot1 But i want in this way screen shot2 Below is the query i am using.enter image description here Please suggest me how can i use regular expressions to get my desired output.

Upvotes: 0

Views: 231

Answers (1)

stefan
stefan

Reputation: 2252

Maybe you can use a combination of REGEXP_SUBSTR() and REPLACE().

Test data:

create table messages ( message_ )
as
select 'High SMS usage - VPMN: PHLGT - SRC: NRT - SDR: 22.64 - Will Report' from dual union all
select 'High SMS usage - VPMN: ABCDE - SRC: ABCD - SDR: 222.64 - Will Report' from dual union all
select 'High SMS usage - VPMN: FGHI - SRC: EFG - SDR: 2222.64 - Will Report' from dual union all
select 'High SMS usage - VPMN: JKL - SRC: HIJK - SDR: 222.64 - Will Report' from dual union all
select 'High SMS usage - VPMN: MNOPQR - SRC: LMN - SDR: 22.64 - Will Report' from dual ;

Query:

select
  replace( regexp_substr( noblanks, '[^-]+', 1, 2), 'VPMN:' ) as VPMN 
, replace( regexp_substr( noblanks, '[^-]+', 1, 3), 'SRC:'  ) as DATA_SRC
, replace( regexp_substr( noblanks, '[^-]+', 1, 4), 'SDR:'  ) as SDR_VALUE
, text_
from (
  select 
    replace( message_, ' ' ) as noblanks  -- message without blanks
  , message_ as text_                     -- original message
  from messages 
) ;

-- result
VPMN    DATA_SRC  SDR_VALUE  TEXT_                                                                 
PHLGT   NRT       22.64      High SMS usage - VPMN: PHLGT - SRC: NRT - SDR: 22.64 - Will Report    
ABCDE   ABCD      222.64     High SMS usage - VPMN: ABCDE - SRC: ABCD - SDR: 222.64 - Will Report  
FGHI    EFG       2222.64    High SMS usage - VPMN: FGHI - SRC: EFG - SDR: 2222.64 - Will Report   
JKL     HIJK      222.64     High SMS usage - VPMN: JKL - SRC: HIJK - SDR: 222.64 - Will Report    
MNOPQR  LMN       22.64      High SMS usage - VPMN: MNOPQR - SRC: LMN - SDR: 22.64 - Will Report

Explanation: the inline view (subquery) gives us the message without blanks/spaces, and the original message text. We then use regexp_substr() to find the key-value pairs, and replace() to remove the "labels" (VPMN:, SRC:, SDR:). Dbfiddle here.

Upvotes: 1

Related Questions