CODEWITHSUNDEEP
sqloracle-database
stack user
stack user

Reputation: 300

Oracle String Conversion

Need help in converting the following string into a required format. I will have several values as below. Is there a easy way to do this using REGEXP or something better?

Current format coming from column A

Region[Envionment Lead|||OTC|||06340|||List Program|||TX|||Z3452|||Souther Region 05|||M7894|||California Divison|||Beginning]

Region[Coding Analyst|||BA|||04561|||Water Bridge|||CA|||M8459|||West Region 09|||K04956|||East Division|||Supreme]

Required Format of column A

Region[actingname=Envionment Lead,commonid=OTC,insturmentid=06340,commonname=List Program]

Region[actingname=Coding Analyst,commonid=BA,insturmentid=04561,commonname=Water Bridge]

revised data 

**Column data**  
Region[Coding Analyst|||BA|||reg pro|||04561|||08/16/2011|||Board member|||AZ|||06340|||Whiter Bridge|||CA|||M0673|||West Region 09|||K04956|||East Division|||Supreme]

**required Data** 
{actingname=06340, actingid=M0673, insturmentid=BA, insturmentname=Coding Analyst, commonname=West Region 09, stdate=08/16/2011, linnumber=04561, linstate=CA, linname=Supreme}

The issue is getting the 10,11,12 and 15 position of the string. I can get anything below 10th position, but not 10 or more string position. Can you please guide me what i'm i missing here 

'{actingname=\8,actingid=\11,insturmentid=\2,insturmentname=\1,commonname=\12, stdate=\5,linnumber=4,linstate=10,linname=15}'--Here 10,11,12 and 15 posistion are not being fethched

Upvotes: 1

Views: 101

Answers (3)

Nikhil
Nikhil

Reputation: 3950

this will work:

select substr(regexp_replace(regexp_replace(regexp_replace
(regexp_replace(regexp_replace("col1",'\[','[actingname='),
                     '\|\|\|',',commonid=',1,1,'i'),
'\|\|\|',',insturmentid=',1,1,'i'),
'\|\|\|',',commonname=',1,1,'i'),
    '\|',']',1,1,'i'),
             1,regexp_instr(regexp_replace(regexp_replace(regexp_replace
(regexp_replace(regexp_replace("col1",'\[','[actingname='),
                     '\|\|\|',',commonid=',1,1,'i'),
'\|\|\|',',insturmentid=',1,1,'i'),
'\|\|\|',',commonname=',1,1,'i'),
    '\|',']',1,1,'i'),'\]')-1            )||']'
from Table1;

check: http://sqlfiddle.com/#!4/3ddfa0/11

thanks!!!!!!

Upvotes: 0

Joakim Danielson
Joakim Danielson

Reputation: 51882

I used REGEXP_REPLACE

SELECT REGEXP_REPLACE(
    'Region[Envionment Lead|||OTC|||06340|||List Program|||TX|||Z3452|||Souther Region 05|||M7894|||California Divison|||Beginning]',
    '^Region\[([[:alpha:][:space:][:digit:]]*)\|\|\|([[:alpha:]]*)\|\|\|([[:digit:]]*)\|\|\|([[:alpha:][:space:][:digit:]]*).*',
    'Region[actingname=\1,commonid=\2,instrumentid=\3,commonname=\4]') as replaced
FROM dual

or like an update it would be

UPDATE table1 
SET col1 = REGEXP_REPLACE(
             col1,
             '^Region\[([[:alpha:][:space:][:digit:]]*)\|\|\|([[:alpha:]]*)\|\|\|([[:digit:]]*)\|\|\|([[:alpha:][:space:][:digit:]]*).*',
             'Region[actingname=\1,commonid=\2,instrumentid=\3,commonname=\4]')

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You can use regexp_substr and listagg consecutively

with t1(str1) as
(
 select 'Region[Coding Analyst|||BA|||04561|||Water Bridge]' from dual   
),   t2(str2) as
(
 select 'actingname,commonid,insturmentid,commonname' from dual   
),   t3 as
(
select regexp_substr(str1, '[^|||]+', 1, level) str1,
       regexp_substr(str2, '[^,]+', 1, level)||'=' str2,
       level as lvl
  from t1
  cross join t2 
connect by level <= regexp_count(str1, '[^|||]+')
),   t4 as
(
select case when lvl = 1 then 
                 replace(str1,'[','['||str2)
       else
                 str2||str1
       end as str, lvl
  from t3
)    
select listagg(str,',') within group (order by lvl) as "Result String" from t4;

Result String
----------------------------------------------------------------------------------------
Region[actingname=Coding Analyst,commonid=BA,insturmentid=04561,commonname=Water Bridge]

P.S. I considered the second one as a sample, and took the 4 first string due to number of substrings seperated by triple-pipes due to the number of tuple labels ending with equality sign is 4. Demo

Upvotes: 0

Related Questions