How to replace specific values in a oracle database column?

SqlOracleReplace

Sql Problem Overview

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

Sql Solutions

Solution 1 - Sql

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 - Sql

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

Solution 3 - Sql

In Oracle, there is the concept of schema name, so try using this

update schemname.tablename t
set t.columnname = replace(t.columnname, t.oldvalue, t.newvalue);

Solution 4 - Sql

I'm using Version 4.0.2.15 with Build 15.21

For me I needed this:

UPDATE table_name SET column_name = REPLACE(column_name,"search str","replace str");

Putting t.column_name in the first argument of replace did not work.

Categories
Recommended Sql Solutions
Recommended Oracle Solutions
Recommended Replace Solutions

Previous Solution:

how to make log4j to write to the console as well

JavaApacheLoggingLog4j

Next Solution:

gradle - how do I build a jar with a lib dir with other jars in it?

JarGradleUberjar

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionscharView Question on Stackoverflow
Solution 1 - SqlOMG PoniesView Answer on Stackoverflow
Solution 2 - SqlBabatunde AdeyemiView Answer on Stackoverflow
Solution 3 - SqlPraveen MishraView Answer on Stackoverflow
Solution 4 - SqlThomas Robert HornView Answer on Stackoverflow