Simplifying a update with replace function DML.
Is there a simplified way to turn below code (sample)
update A
set TERMS = trim(replace(TERMS,'-',','))
/
update A
set TERMS = trim(replace(TERMS,'A','B'))
/
update A
set TERMS = trim(replace(TERMS,'C','D'))
/
update A
set TERMS = trim(replace(TERMS,'E','F'))
/
update A
set TERMS = trim(replace(TERMS,'111','222'))
/
update A
set TERMS = trim(replace(TERMS,'......','......'))
Turn this code to a single script.
You can use TRANSLATE for changes to single characters (and do not need to specify the complete alphabet, just the characters you are translating) and can nest the REPLACE statements:
update A
SET TERMS = TRIM(
REPLACE(
REPLACE(
TRANSLATE( TERMS, 'ACE-', 'BDF,' ),
'111',
'222'
),
'......',
'......'
)
)
db<>fiddle here
TRANSLATE.Chain you replace like this:
update A set terms=trim(replace(replace(terms,'A','B'),'C','D'));
Well, you could nest the calls to UPDATE:
update A
set TERMS = trim(replace(replace(
replace(replace(
replace(TERMS, '-', ','), 'A', 'B'), 'C', 'D'), 'E', 'F'), '111', '222'))
The other obvious possible option would be a regex replacement. But, that wouldn't help much here, because even though we could easily phrase all targets in a single regex, each target has a separate replacement.
You can nest replace() or use translate(). If your string is all alpha-numeric with a few other characters:
update a
set TERMS = trim(replace(translate(terms, '- ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ', BBDDFFGHIJKLMNOPQRSTUVWXYZ0123456789'), '111', '222'))
set A should be set TERMS and the two strings in TRANSLATE have a different length which leads to mess of data. *Generel teh use of TRANSLATE is IMO a good idea!
REPLACE(TERMS,'......','......)supposed to do? This will simply replace six consecutive period characters with six consecutive period characters. Seems kind of pointless to me. ??? I'd also like to suggest that the long version above is better code, because it's clear what's being done.