How to replace specific values in a oracle database column?

Question

I am looking to replace values in a particular column. For example the following column values

column name
----------
Test1
Test2
Test3
Test12

should be (replacing est1 with rest1)

column name
----------
Trest1
Test2
Test3
Trest12

Solution 1

Use REPLACE:

SELECT REPLACE(t.column, 'est1', 'rest1')
  FROM MY_TABLE t

If you want to update the values in the table, use:

UPDATE MY_TABLE t
   SET column = REPLACE(t.column, 'est1', 'rest1')

Solution 2

If you need to update the value in a particular table:

UPDATE TABLE-NAME SET COLUMN-NAME = REPLACE(TABLE-NAME.COLUMN-NAME, 'STRING-TO-REPLACE', 'REPLACEMENT-STRING');

where

  TABLE-NAME         - The name of the table being updated
  COLUMN-NAME        - The name of the column being updated
  STRING-TO-REPLACE  - The value to replace
  REPLACEMENT-STRING - The replacement

Comments

[Tom Shaw]

In Oracle this should be UPDATE t, not UPDATE TABLE t.

[nbrooks]

@Tom Presumably 'TABLE' is used here as the fictional table name (and 't' is the alias). The select statement is also getting values from a similarly named fictional table.