0

I need help with replacing column value to replace all '%' delimiter with '~'.

Example: 

CTP%3A1c5d8384-5980-4bee-88f5-aed7594594ba%7ESV%3ANA%7ECUST%3Anew%7ECSR%3AGREATER+BOSTON+MARKET%7ECSD%3ANORTHEAST+DIVISION%7EZIP%3A02140%7EBFT%3AAll+digital+buyflow%7Cdotcom%7EBPT%3ANA%7EIDV%3ANA%7EMRC%3ANA%7ESIKE%3ANA%7ESIKP%3ANA%7ESIKS%3ANA%7ERAF%3Ano
CTP%3A7ef7b00a-10dc-41e9-8691-0a01463703c9%7ESV%3ANA%7ECUST%3Aexisting%7ECSR%3ABIG+SOUTH+REGION%7ECSD%3ACENTRAL+DIVISION%7EZIP%3A30101%7EBFT%3AAll+digital+buyflow%7Cdotcom%7EBPT%3ANA%7EIDV%3ANA%7EMRC%3ANA%7ESIKE%3ANA%7ESIKP%3ANA%7ESIKS%3ANA%7ERAF%3Ano
CTP%3A9ed9ba57-4227-444a-9385-101bbc0df3bc%7ESV%3ANA%7ECUST%3Anew%7ECSR%3ABIG+SOUTH+REGION%7ECSD%3ACENTRAL+DIVISION%7EZIP%3A30047%7EBFT%3AAll+digital+buyflow%7Cdotcom%7EBPT%3ANA%7EIDV%3ANA%7EMRC%3ANA%7ESIKE%3ANA%7ESIKP%3ANA%7ESIKS%3ANA%7ERAF%3Ano
CTP%3Ae46e7133-340d-41b9-9cdf-2baea14c86b6%7ESV%3ANA%7ECUST%3Aexisting%7ECSR%3ASEATTLE+MARKET%7ECSD%3AWEST+DIVISION%7EZIP%3A98223%7EBFT%3AAddOnChannel%7EBPT%3ANA%7EIDV%3ANA%7EMRC%3ANA%7ESIKE%3ANA%7ESIKP%3ANA%7ESIKS%3ANA%7ERAF%3Ano

Expected Output:
CTP~3A1c5d8384-5980-4bee-88f5-aed7594594ba~7ESV~3ANA~7ECUST~3Anew~7ECSR~3AGREATER+BOSTON+MARKET~7ECSD~3ANORTHEAST+DIVISION~7EZIP~3A02140~7EBFT~3AAll+digital+buyflow~7Cdotcom~7EBPT~3ANA~7EIDV~3ANA~7EMRC~3ANA~7ESIKE~3ANA~7ESIKP~3ANA~7ESIKS~3ANA~7ERAF~3Ano
CTP~3A7ef7b00a-10dc-41e9-8691-0a01463703c9~7ESV~3ANA~7ECUST~3Aexisting~7ECSR~3ABIG+SOUTH+REGION~7ECSD~3ACENTRAL+DIVISION~7EZIP~3A30101~7EBFT~3AAll+digital+buyflow~7Cdotcom~7EBPT~3ANA~7EIDV~3ANA~7EMRC~3ANA~7ESIKE~3ANA~7ESIKP~3ANA~7ESIKS~3ANA~7ERAF~3Ano
CTP~3A9ed9ba57-4227-444a-9385-101bbc0df3bc~7ESV~3ANA~7ECUST~3Anew~7ECSR~3ABIG+SOUTH+REGION~7ECSD~3ACENTRAL+DIVISION~7EZIP~3A30047~7EBFT~3AAll+digital+buyflow~7Cdotcom~7EBPT~3ANA~7EIDV~3ANA~7EMRC~3ANA~7ESIKE~3ANA~7ESIKP~3ANA~7ESIKS~3ANA~7ERAF~3Ano
CTP~3Ae46e7133-340d-41b9-9cdf-2baea14c86b6~7ESV~3ANA~7ECUST~3Aexisting~7ECSR~3ASEATTLE+MARKET~7ECSD~3AWEST+DIVISION~7EZIP~3A98223~7EBFT~3AAddOnChannel~7EBPT~3ANA~7EIDV~3ANA~7EMRC~3ANA~7ESIKE~3ANA~7ESIKP~3ANA~7ESIKS~3ANA~7ERAF~3Ano

I have tried to use OREPLACE but it didn't work.

UPDATE A 
SET ORDER_INFO = OREPLACE(ORDER_INFO,'%','~') 
WHERE ORDER_INFO NOT LIKE '%~%' AND CTP_SESSION_ID IS NULL;
GMB
  • 216,147
  • 25
  • 84
  • 135
Debasis Das
  • 37
  • 5
  • Can you clarify what "it didn't work" means? What did happen? Two things to check: If you use the same WHERE clause in a SELECT, does it return the rows you intend to update? Do you have a user-defined OREPLACE in the SYSLIB database? Try using qualified name TD_SYSFNLIB.OREPLACE – Fred Nov 15 '22 at 00:50

1 Answers1

0
UPDATE A 
SET ORDER_INFO = OREPLACE(ORDER_INFO,'%','~') 
WHERE ORDER_INFO NOT LIKE '%~%' 
AND CTP_SESSION_ID IS NULL;
Debasis Das
  • 37
  • 5
  • As you replace just a singe character it's probably better to use `oTranslate` instead of `oReplace` – dnoeth Nov 15 '22 at 21:34
  • Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Jeremy Caney Nov 16 '22 at 00:12