Find and Replace a strings in oracle

Question

I have a string which contains data as follows:

'ID,MASTER_ID,DATA_SOURCE_ID,DATA_SOURCE_NAME,CHILD_COUNT,CHILD_COUNT_TEXT,PARENT_OR_CHILD,RECORD_TYPE,FULL_NAME_LNF,FULL_NAME_FNF,FIRST_NAME,LAST_NAME,PREFIX,SUFFIX,MIDDLE,TITLE,NAME_OF_ORGANIZATION,NAME_OF_BUSINESS,TYPE_OF_ENTITY,ADDRESS,CITY,STATE,PROVINCE,POSTAL_CODE,COUNTRY,POSTAL_ADDRESS_TYPE,PHONE_AREA_CODE,PHONE_NUMBER,PHONE_COUNTRY_CODE,PHONE,PHONE_TYPE,EMAIL_ADDRESS,URL,HCP_SPECIALTY,HCP_TYPE,HCP_SUBTYPE,RECIPIENT_STATUS,COVERED_RECIPIENT_FLAG,RELATIONSHIP_TO_CR,LAST_MODIFIED_BY,LAST_MODIFIED_DATE,PRIMARY_LICENSE_STATE_AR,PRIMARY_LICENSE_NUM_AR,DEA_REG_NUM_AR,NPI_NUM_AR,UPIN_AR,TAX_PAYER_ID_AR,PRIMARY_LICENSE_STATE_CR,PRIMARY_LICENSE_NUM_CR,DEA_REG_NUM_CR,NPI_NUM_CR,UPIN_CR,DEA_NUMBER,NPI,UPIN,TIN,TAX_PAYER_ID_CR,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,ATTRIBUTE21,ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,ATTRIBUTE25,ATTRIBUTE26,ATTRIBUTE27,ATTRIBUTE28,ATTRIBUTE29,ATTRIBUTE30,SOURCE_REGION_CODE,SOURCE_SYSTEM_CODE,REC_INVALID_FLAG,REVISION_FLAG,IS_ACTIVE,PROCESS_STATE,RECIPIENT_CATEGORY01,RECIPIENT_CATEGORY02,RECIPIENT_CATEGORY03,RECIPIENT_CATEGORY04,RECIPIENT_CATEGORY05,RECIPIENT_CATEGORY06,RECIPIENT_CATEGORY07,RECIPIENT_CATEGORY08,RECIPIENT_CATEGORY09,RECIPIENT_CATEGORY10,RECIPIENT_CATEGORY11,RECIPIENT_CATEGORY12,RECIPIENT_CATEGORY13,RECIPIENT_CATEGORY14,RECIPIENT_CATEGORY15,RECIPIENT_CATEGORY16,RECIPIENT_CATEGORY17,RECIPIENT_CATEGORY18,RECIPIENT_CATEGORY19,RECIPIENT_CATEGORY20,RECIPIENT_CATEGORY21,RECIPIENT_CATEGORY22,RECIPIENT_CATEGORY23,RECIPIENT_CATEGORY24,RECIPIENT_CATEGORY25,RECIPIENT_CATEGORY26,RECIPIENT_CATEGORY27,RECIPIENT_CATEGORY28,RECIPIENT_CATEGORY29,RECIPIENT_CATEGORY30,IS_PICKABLE,IS_GOLDEN,PRIMARY_LICENSE_NUM,PRIMARY_LICENSE_EFFECTIVE,PRIMARY_LICENSE_EXPIRES,TERTIARY_LICENSE_EFFECTIVE,TERTIARY_LICENSE_EXPIRES,SECONDARY_LICENSE_EFFECTIVE,SECONDARY_LICENSE_EXPIRES,SECONDARY_LICENSE_NUM,TERTIARY_LICENSE_NUM,ADDRESS2,PHONE_AREA_CODE2,PHONE_NUMBER2,PHONE_COUNTRY_CODE2,PHONE_TYPE2,TERRITORY,PRIMARY_AFFILIATION,PRIMARY_AFFILIATION_STATE,REQUEST_WF_STATE,IS_EDIT_LOCKED,SOURCE_SYSTEM_RECIPIENT_ID,CREATED_BY,CREATION_DATE,APPROVER_COMMENTS,SECONDARY_LICENSE_STATE,PRIMARY_LICENSE_STATE,NPI_DATA,STATE_DATA,DEA_DATA,RPPS,FINESS,SIREN_NUMBER,DPC'

Can anybody tell me how to find only the following values

DATA_SOURCE_ID,   
       LAST_MODIFIED_BY,
       LAST_MODIFIED_DATE,
       ATTRIBUTE13,
       ATTRIBUTE14,
       ATTRIBUTE15,
       ATTRIBUTE16,
       ATTRIBUTE17,
       ATTRIBUTE18,
       ATTRIBUTE19,
       ATTRIBUTE20,
       ATTRIBUTE21,
       ATTRIBUTE22,
       ATTRIBUTE23,
       ATTRIBUTE24,
       ATTRIBUTE25,
       ATTRIBUTE26,
       ATTRIBUTE27,
       ATTRIBUTE28,
       ATTRIBUTE29,
       ATTRIBUTE30,

And then replace them with following strings

'L.DATA_SOURCE_ID,L.LAST_MODIFIED_BY,L.LAST_MODIFIED_DATE,L.ATTRIBUTE13,L.ATTRIBUTE14,L.ATTRIBUTE15,L.ATTRIBUTE16,L.ATTRIBUTE17,L.ATTRIBUTE18,L.ATTRIBUTE19,L.ATTRIBUTE20,L.ATTRIBUTE21,L.ATTRIBUTE22,L.ATTRIBUTE23,L.ATTRIBUTE24,L.ATTRIBUTE25,L.ATTRIBUTE26,L.ATTRIBUTE27,L.ATTRIBUTE28,L.ATTRIBUTE29,L.ATTRIBUTE30,'

I fail to see how a database has anything to do with this. If you have a string and want to look at string parts use a programming language to parse it. If you have this string in a database table then the DBMS should be oblivious to its parts and meaning. If on the other hand you want the string's content in a database then don't store it as a string but as separate columns. — Thorsten Kettner
– Thorsten Kettner, Commented Oct 15, 2015 at 9:47

james jelo4kul · Accepted Answer · 2015-10-15 10:59:36Z

1

You are looking for this. It gives you your desired result

SELECT mycol, CONCAT('L.', mycol) AS newCol
 FROM(SELECT * FROM test WHERE mycol REGEXP 
'DATA_SOURCE_ID|LAST_MODIFIED_BY|LAST_MODIFIED_DATE|ATTRIBUTE[0-9]+') 
        as temp

EXPLANATION

SELECT * FROM test WHERE mycol REGEXP 
    'DATA_SOURCE_ID|LAST_MODIFIED_BY|LAST_MODIFIED_DATE|ATTRIBUTE[0-9]+')

This finds all rows that either have DATA_SOURCE_ID or LAST_MODIFIED_BY or LAST_MODIFIED_DATE or ATTRIBUTE followed by any number

SELECT mycol, CONCAT('L.', mycol) AS newCol

This adds L. to all the rows that has been found by the subquery.

OUTPUT

L.DATA_SOURCE_ID,
L.LAST_MODIFIED_BY,
L.LAST_MODIFIED_DATE,
L.ATTRIBUTE13,
L.ATTRIBUTE14,
L.ATTRIBUTE15,
L.ATTRIBUTE16,
L.ATTRIBUTE17,
L.ATTRIBUTE18,
L.ATTRIBUTE19,
L.ATTRIBUTE20,
L.ATTRIBUTE21,
L.ATTRIBUTE22,
L.ATTRIBUTE23,
L.ATTRIBUTE24,
L.ATTRIBUTE25,
L.ATTRIBUTE26,
L.ATTRIBUTE27,
L.ATTRIBUTE28,
L.ATTRIBUTE29,
L.ATTRIBUTE30

Hope this helps

edited Oct 15, 2015 at 10:59

answered Oct 15, 2015 at 10:47

james jelo4kul

8294 silver badges17 bronze badges

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

1 Comment

Burhan Khalid Butt Over a year ago

Thank you for time and help. I used a type and a function to populate it.

Rahul Tripathi · Accepted Answer · 2015-10-15 09:27:22Z

1

You can try like this:

replace( replace(myString, 'DATA_SOURCE_ID', 'yourReplaceValue1'), 'LAST_MODIFIED_BY', yourReplaceValu2).....

This ..... is for you to make the similar replace for the rest of your replacing strings :)

answered Oct 15, 2015 at 9:27

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

1 Comment

Burhan Khalid Butt Over a year ago

Thank you for your time.

Lalit Kumar B · Accepted Answer · 2015-10-15 09:31:19Z

1

Use nested REPLACE:

SQL> WITH data AS
  2    ( SELECT 'a,b,c' str FROM dual
  3    )
  4  SELECT str,
  5    REPLACE(REPLACE(REPLACE(str, 'a', 'x'), 'y'), 'z') new_str
  6  FROM data;

STR   NEW_S
----- -----
a,b,c x,b,c

answered Oct 15, 2015 at 9:31

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

3 Comments

Burhan Khalid Butt Over a year ago

Thank you for your time.

Lalit Kumar B Over a year ago

@BurhanKhalidButt You're welcome. However, remember, you need to do these redundant tasks outside the database.

Burhan Khalid Butt Over a year ago

It was not possible for me to do these changes outside database. As the front programmer said it was difficult for him to do such a change. However, by your and the help of other members' I was able to crack it :)

Collectives™ on Stack Overflow

Find and Replace a strings in oracle

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related