REGEXP_REPLACE Error in substituting a string

Question

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'

MT0 · Accepted Answer · 2019-05-08 11:49:14Z

1

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

answered May 8, 2019 at 11:49

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

stack user Over a year ago

@MT0 Thank you sir. almost there.. no I get 2 issues with this. My bad i didn't specify originally. I also have some rows null and text 'null'. when i modified your query as above amended. i get error "ORA-00932: inconsistent datatypes: expected CHAR got CLOB" . I need to modify only when there is a '|||' delimited string

Collectives™ on Stack Overflow

REGEXP_REPLACE Error in substituting a string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related