CODEWITHSUNDEEP
sqlregexoracle-database
stack user
stack user

Reputation: 300

REGEXP_REPLACE Error in substituting a string

I'm looking to convert the following string into a required format. I will have several values as below. Is there a easy way to do this. I tried using REGEXP_REPLACE and couldn't get it work:

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 missing here

 SELECT REGEXP_REPLACE(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],'^Region\[([[:alpha:][:space:][:digit:]]*)\|\|\|([[:alpha:]]*)\|\|\|([[:alpha:][:space:][:punct:]]*)\|\|\|([[:digit:][:alpha:]]*)\|\|\|([[:digit:][:punct:]]*)\|\|\|([[:alpha:][:space:]]*)\|\|\|([[:alpha:]]*)\|\|\|([[:digit:]]*)\|\|\|([[:alpha:][:space:]]*)\|\|\|([[:alpha:]]*)\|\|\|([[:digit:][:alpha:]]*)\|\|\|([[:digit:][:alpha:][:space:]]*)\|\|\|([[:digit:][:alpha:]]*)\|\|\|([[:alpha:][:space:]]*)\|\|\|([[:alpha:]]*).*','{actingname=\8,actingid=\11,insturmentid=\2,insturmentname=\1,commonname=\12, stdate=\5,linnumber=4,linstate=10,linname=15}']') as replaced
    FROM dual;--Here 10,11,12 and 15 position are not being fetched

Amended query as per @MT0 feedback as I have some rows with null and text 'null' as values. I get error ORA-00932: inconsistent datatypes: expected CHAR got CLOB

 SELECT
            '{'
       ||   'actingname='     || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1,  8, NULL, 1 )
       || ', actingid='       || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1, 11, NULL, 1 )
       || ', insturmentid='   || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1,  2, NULL, 1 )
       || ', insturmentname=' || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1,  1, NULL, 1 )
       || ', commonname='     || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1, 12, NULL, 1 )
       || ', stdate='         || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1,  5, NULL, 1 )
       || ', linnumber='      || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1,  4, NULL, 1 )
       || ', linstate='       || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1, 10, NULL, 1 )
       || ', linname='        || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1, 15, NULL, 1 )
       || '}'
       AS replaced
    FROM test_data
where value is not null and value <>'null'

Upvotes: 0

Views: 160

Answers (1)

MT0
MT0

Reputation: 167972

Use REGEXP_SUBSTR:

Oracle Setup:

CREATE TABLE test_data( value ) AS
  SELECT '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],'
  FROM dual;

Query:

SELECT    '{'
       ||   'actingname='     || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1,  8, NULL, 1 )
       || ', actingid='       || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1, 11, NULL, 1 )
       || ', insturmentid='   || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1,  2, NULL, 1 )
       || ', insturmentname=' || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1,  1, NULL, 1 )
       || ', commonname='     || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1, 12, NULL, 1 )
       || ', stdate='         || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1,  5, NULL, 1 )
       || ', linnumber='      || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1,  4, NULL, 1 )
       || ', linstate='       || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1, 10, NULL, 1 )
       || ', linname='        || REGEXP_SUBSTR( value, '([a-zA-Z0-9 ]*)(\|\|\||\])', 1, 15, NULL, 1 )
       || '}' AS replaced
FROM   test_data

Output:

| REPLACED                                                                                                                                                                  |
| :------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| {actingname=06340, actingid=M0673, insturmentid=BA, insturmentname=Coding Analyst, commonname=West Region 09, stdate=2011, linnumber=04561, linstate=CA, linname=Supreme} |

db<>fiddle here

Upvotes: 1

Related Questions