Replace string in a column using query in Oracle

Question

In one of my oracle tables, in one column in every row there is a string 'House Name'. I need to replace it with 'House Number'. Can I execute an update query to find and replace this string in all rows.Or is there any built in function for that.

Answer 1

The following from Tech on the Net may help:

REPLACE('User House Name is ABC', 'House Name', 'House Number');

would return 'User House Number is ABC'

REPLACE('123tech123', '123'); would return 'tech'

REPLACE('222tech', '2', '3'); would return '333tech'

REPLACE('0000123', '0'); would return '123'

REPLACE('House Name', 'House Name', 'House Number'); would return 'House Number'

Answer 2

Just execute :

UPDATE <TABLE-NAME> SET <COLUMN-NAME> = 'House Number' WHERE <COLUMN_MAME> = 'House Name'

This of course will work only if the column contains this string only. Otherwise you should use the replace function as answered by Fayeq in your update statement above

UPDATE <TABLE-NAME> SET <COLUMN-NAME> = REPLACE('House Name', 'Name', 'Number') WHERE <COLUMN_MAME> = 'House Name'

EDIT :

You can omit the WHERE clause if all rows contain the same string (House Number)