How to replace specific word on a large text in a column using Oracle

user_8952081 0
Viewed 241

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

desc
----------
Test1 words = 30 have so much words = 20 over 60 words +
Test2 words = 30 have so much words = 20 over 60 words +
Test3 words = 30 have so much words = 20 over 60 words +
Test4 words = 30 have so much words = 20 over 60 words +

should be (replacing ords = 20 with Alpha = 20)

result:

desc
----------
Test1 words = 30 have so much Alpha = 20 over 60 words +
Test2 words = 30 have so much Alpha = 20 over 60 words +
Test3 words = 30 have so much Alpha = 20 over 60 words +
Test4 words = 30 have so much Alpha = 20 over 60 words +

I have tried the following :

update table set desc = REPLACE('words = 20', 'words', 'Alpha') where ;

This results to :

Alpha = 20 instead of the entire row. however, how can I keep the rest of the text just change "words = 20" but still keep the rest of the test?

oracle11g sql-update sql
2 Answers

You need to perform the replace on the original string, not on `'words = 20':

UPDATE mytable
SET    desc = REPLACE(desc, 'words', 'Alpha')

Side note:
I'm guessing these aren't the real names in your table, but desc is a reserved word in SQL. If it's really the column's name, you need to escape it with double quotes (i.e., "desc").

user_24227760

Your statement:

update table set desc = REPLACE('words = 20', 'words', 'Alpha') where ...;

is replacing 'words' in the fixed string 'words = 20`; it isn't acting on the existing column value you at all.

You could do:

update table set desc = REPLACE(desc, 'words = 20', 'Alpha = 20') where ...;

db<>fiddle (using valid table and column names).

If you want to replace all occurrences of 'words', not just 'words = 20', then remove the = 20 from both arguments; but from your attempt I think you want it to be more targetted.

user_2663040
Related