CODEWITHSUNDEEP
sqloracle-databasefunc
Richie Quinsayas
Richie Quinsayas

Reputation: 27

Simplifying a replace oracle function

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.

Upvotes: 1

Views: 75

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

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'))

Upvotes: 0

MT0
MT0

Reputation: 168671

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

Upvotes: 2

fiveobjects
fiveobjects

Reputation: 4317

Chain you replace like this:

update A set terms=trim(replace(replace(terms,'A','B'),'C','D'));

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522719

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.

Upvotes: 0

Related Questions