Home » SQL & PL/SQL » SQL & PL/SQL » Replace column Value (12c)
Replace column Value [message #686201] Thu, 30 June 2022 11:14 Go to next message
deepakdot
Messages: 77
Registered: July 2015
Member
Hello,

In a table column I have data like Below. I need to replace a specific pattern with a value. lets say,

"Audit:(.....)" need to be replaced with "Audit:null" . The value inside the () will differ for each row.

{Customer:123,code:ABC,Audit:(user:deepak,WS:10,TS:2022-12-23),Dept:20}
need to change to
{Customer:123,code:ABC,Audit:null,Dept:20}

{Customer:235,code:XYZ,Audit:(user:Vikash,WS:12,IP:159,TS:2021-11-21),Dept:30}
need to change to
{Customer:235,code:XYZ,Audit:null,Dept:30}

we might have this pattern occurrence 2 times like below.

{Customer:235,code:XYZ,Audit:(user:Vikash,WS:12,TS:2021-11-21),Dept:30,Audit:(user:Vikash,WS:12,TS:2021-11-21)}
need to change to
{Customer:235,code:XYZ,Audit:null,Dept:30,Audit:null}


Please help me with a SQL.

Thanks
Deepak

Report message to a moderator

Re: Replace column Value [message #686202 is a reply to message #686201] Thu, 30 June 2022 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 
SQL> with
  2    data as (
  3      select '{Customer:123,code:ABC,Audit:(user:deepak,WS:10,TS:2022-12-23),Dept:20}' data from dual union all
  4      select '{Customer:235,code:XYZ,Audit:(user:Vikash,WS:12,IP:159,TS:2021-11-21),Dept:30}' data from dual union all
  5      select '{Customer:235,code:XYZ,Audit:(user:Vikash,WS:12,TS:2021-11-21),Dept:30,Audit:(user:Vikash,WS:12,TS:2021-11-21)}' data from dual
  6    )
  7  select regexp_replace(data,'Audit:\([^)]*\)','Audit:null') res
  8  from data
  9  /
RES
------------------------------------------------------------------------------------------------------------------------
{Customer:123,code:ABC,Audit:null,Dept:20}
{Customer:235,code:XYZ,Audit:null,Dept:30}
{Customer:235,code:XYZ,Audit:null,Dept:30,Audit:null}

Report message to a moderator

Re: Replace column Value [message #686204 is a reply to message #686202] Fri, 01 July 2022 02:37 Go to previous messageGo to next message
deepakdot
Messages: 77
Registered: July 2015
Member
Thanks Michel. This Works. I was not aware of this regexp_replace function.

With the real data, I am messing somewhere with the { [ \ etc and not getting a proper result. Kindly help me with the pattern.


\"~audit\":[{\"Ts1\":\"Apr 22, 2021 4:20:29 PM\"},{\"Offset\":\"-04:00\"},{\"Process\":\"Test1\"},{\"TS2\":\"Apr 22, 2021 4:20:30 PM\"},{\"TS3\":\"Apr 22, 2021 4:20:29 PM\"},{\"user\":\"deepak\"},{\"id\":\"a5317880\"},{\"WS\":\"UI\"}]
Need to change to
\"~audit\":null

Report message to a moderator

Re: Replace column Value [message #686205 is a reply to message #686204] Fri, 01 July 2022 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So this is no more "Audit:(.....)".
What is the COMPLETE, ACCURATE and CORRECT definition of the possible data?
Otherwise I can provide you many solutions and you will say it doesn't work with ... each time.

Report message to a moderator

Re: Replace column Value [message #686206 is a reply to message #686205] Fri, 01 July 2022 05:19 Go to previous messageGo to next message
deepakdot
Messages: 77
Registered: July 2015
Member

The column data format would be like below , The data include \ and " . Hope this is clear .



Actual Data:
'Customer:[Deepak],\"~audit\":[{\"Ts1\":\"Apr 22, 2021 4:20:29 PM\"},{\"Offset\":\"-04:00\"},{\"Process\":\"Test1\"},{\"TS2\":\"Apr 22, 2021 4:20:30 PM\"},{\"TS3\":\"Apr 22, 2021 4:20:29 PM\"},{\"user\":\"deepak\"},{\"id\":\"a5317880\"},{\"WS\":\"UI\"}],Customer2:[Deepak2]')

need to change to
'Customer:[Deepak],\"~audit\":null,Customer2:[Deepak2]');

So \"~audit\":[.......] Need to change to \"~audit\":null



Deepak

Report message to a moderator

Re: Replace column Value [message #686207 is a reply to message #686206] Fri, 01 July 2022 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is this the only case? What about the parentheses in the first post? is ~ always there? Does the data ALWAYS include \ and "?...

Report message to a moderator

Re: Replace column Value [message #686208 is a reply to message #686207] Fri, 01 July 2022 06:15 Go to previous messageGo to next message
deepakdot
Messages: 77
Registered: July 2015
Member
The first post was just a sample , not the real data. Yes. this is the only case and the data always include \ and " and ~ as I have given.

Thank you
Deepak

Report message to a moderator

Re: Replace column Value [message #686209 is a reply to message #686208] Fri, 01 July 2022 07:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
Senior Member
 
with sample as (
                select 'Customer:[Deepak],\"~audit\":[{\"Ts1\":\"Apr 22, 2021 4:20:29 PM\"},{\"Offset\":\"-04:00\"},{\"Process\":\"Test1\"},{\"TS2\":\"Apr 22, 2021 4:20:30 PM\"},{\"TS3\":\"Apr 22, 2021 4:20:29 PM\"},{\"user\":\"deepak\"},{\"id\":\"a5317880\"},{\"WS\":\"UI\"}],Customer2:[Deepak2]' data from dual
               )
select  regexp_replace(data,'(\\"~audit\\":\[[^]]+\])','\"~audit\":null') new_data
  from  sample
/

NEW_DATA
-----------------------------------------------------
Customer:[Deepak],\"~audit\":null,Customer2:[Deepak2]

SQL>
SY.

Report message to a moderator

Re: Replace column Value [message #686211 is a reply to message #686209] Fri, 01 July 2022 08:59 Go to previous message
deepakdot
Messages: 77
Registered: July 2015
Member
Thank you.

Report message to a moderator

Previous Topic: SENDGRID | Curl
Next Topic: Teachers teaching the most students
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Mar 28 10:12:13 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.