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.
1
-
yu can do it with update queryAmit Bisht– Amit Bisht2013-09-06 07:24:30 +00:00Commented Sep 6, 2013 at 7:24
Add a comment
|
2 Answers
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'
3 Comments
Digital Alchemist
@Tom Sebastian use this in your Select or update query... will work for you
Tom Sebastian
Thanks. I used REPLACE function like this: update PATIENT set ADDRESS = REPLACE(ADDRESS , 'House Name', 'House Number'); then ADDRESS column of all rows get updated correctly.
Ben
Hi, when quoting from somewhere else please ensure that you grant them credit; it's plagiarism if you don't.
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)
4 Comments
Tom Sebastian
Ok. can we avoid the where clause?
giorashc
Actually you can unless some rows contain another string (which then will override them as well).
Tom Sebastian
i meant to use like this. REPLACE(<COLUMN_NAME>, 'House Name', 'House Number') .I'm not sure but it's working for me.
Amrit
why does it take forever to update a single value