CODEWITHSUNDEEP
oracle-database
mfrancis
mfrancis

Reputation: 13

Using REPLACE within Oracle CASE statement

mytext is a column in mytable containing the values ('ABC123','ABC456','Alphabet 123','Alphabet 456', 'DEF123' ...), but I want them all to be formatted as 'ABC123'. I can't get the syntax right

UPDATE mytable 
  SET mytext = 
     CASE
       WHEN (mytext like 'Alphabet %') Then REPLACE(mytext, 'Alphabet ', 'ABC')
       WHEN (mytext like 'Dookickey %') Then REPLACE(mytext, 'Doohickey ', 'DEF')
       WHEN (mytext like 'Goofiness %') Then REPLACE(mytext, 'Goofiness ', 'GHI')
       ELSE -- I want no other rows to be processed
     END;

Upvotes: 1

Views: 7010

Answers (2)

A B
A B

Reputation: 4158

You can use the SUBSTR function in the CASE statement to check the first N characters.

You may have to adjust the number of characters being compared: in this example, the CASE will match only up to the first 8 characters:

    select CASE SUBSTR("FIELD1", 1, 8) 
           --    12345678
           WHEN 'OLD_TEXT'    THEN REPLACE("FIELD1", 'OLD_TEXT', 'NEW_TEXT')
           WHEN 'ALPHABET'    THEN REPLACE("FIELD1", 'ALPHABET', 'ABC')
           ELSE "FIELD1"   -- For all other values, leave unchanged
           END
    from
    ( 
      select
      'OLD_TEXT1' "FIELD1"
      FROM DUAL
      UNION
      select
      'OLD_TEXT2' "FIELD1"
      FROM DUAL
      UNION
      select
      'ALPHABET' "FIELD1"
      FROM DUAL
      UNION
      select
      'DNR1' "FIELD1"
      FROM DUAL
      UNION
      select
      'DNR2_DO_NOT_REPLACE' "FIELD1"
      FROM DUAL
    )  ;

Upvotes: 0

Sylvain Leroux
Sylvain Leroux

Reputation: 52040

Simply using:

 UPDATE mytable SET mytext = REPLACE(mytext, 'Alphabet ', 'ABC')

Should work as REPLACE will leave the value intact if there is no match. However, this will touch all rows in your table, replacing some values by themselves -- which, at the very least, is not very efficient. In addition, this will replace any occurrence of the world Alphabet, even if not at the start of the string.

If you need something more robust, you should add a filter:

 UPDATE mytable SET mytext = REPLACE(mytext, 'Alphabet ', 'ABC')
   WHERE mytext LIKE 'Alphabet %'

See http://sqlfiddle.com/#!4/16512/1

As the question was edited to support multiple replacement strings, you could indeed use a CASE statement:

UPDATE mytable 
  SET mytext = 
     CASE
       WHEN (mytext LIKE 'Alphabet %') THEN REPLACE(mytext, 'Alphabet ', 'ABC')
       WHEN (mytext LIKE 'Dookickey %') THEN REPLACE(mytext, 'Dookickey ', 'DEF')
       WHEN (mytext LIKE 'Goofiness %') THEN REPLACE(mytext, 'Goofiness ', 'GHI')
       ELSE mytext
    --      ^^^^^^
    --   let unchanged
     END;

See http://sqlfiddle.com/#!4/31531c/1

As with my first solution, this will touch all rows of your table. And as before again, you can use a filter to avoid that. In that case, the ELSE clause is not longer necessary (since non-matching rows are left untouched):

UPDATE mytable 
  SET mytext = 
     CASE
       WHEN (mytext LIKE 'Alphabet %') THEN REPLACE(mytext, 'Alphabet ', 'ABC')
       WHEN (mytext LIKE 'Dookickey %') THEN REPLACE(mytext, 'Dookickey ', 'DEF')
       WHEN (mytext LIKE 'Goofiness %') THEN REPLACE(mytext, 'Goofiness ', 'GHI')
     END
  WHERE mytext LIKE 'Alphabet %' 
     OR mytext LIKE 'Dookickey %' 
     OR mytext LIKE 'Goofiness %';

See http://sqlfiddle.com/#!4/44846/1

Upvotes: 2

Related Questions