sqlsql-updateteradatateradata-sql-assistantteradatasql

Updating column value to replace a particular character in Teradata

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;
Solution 
  • UPDATE A 
SET ORDER_INFO = OREPLACE(ORDER_INFO,'%','~') 
WHERE ORDER_INFO NOT LIKE '%~%' 
AND CTP_SESSION_ID IS NULL;