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
-
I looked at the search results and found ones that use replace to get values from a dual table. I am looking to update the table as opposed to get values.schar– schar2010-08-09 18:47:58 +00:00Commented Aug 9, 2010 at 18:47
Add a comment
|
4 Answers
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')
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
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);
Comments
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.
