CODEWITHSUNDEEP
sqloracle-databasestr-replace
Tom Sebastian
Tom Sebastian

Reputation: 3433

Replace string in a column using query in Oracle

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.

Upvotes: 4

Views: 35385

Answers (2)

giorashc
giorashc

Reputation: 13713

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)

Upvotes: 4

Digital Alchemist
Digital Alchemist

Reputation: 2332

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'

Upvotes: 6

Related Questions