Home » SQL & PL/SQL » SQL & PL/SQL » How to Change One word in a particular column
How to Change One word in a particular column [message #189804] Sun, 27 August 2006 23:30 Go to next message
vikasg2006
Messages: 78
Registered: March 2006
Location: Dubai
Member
Hello,
I want to change in one word of a particular column with another word in whole sentance. For example there is one table in which one field name is narration. And if one narration is "Being amount paid to vikas for furniture purchase". OK, Now there is n nos of record in table in which vikas is appears many times in narration. I want to change the word vikas to manish wherever it appears in narration and rest of the narration remains unchanged . Is it possible? if yes then how can we do this.
thanks in advance

Vikas gupta
Re: How to Change One word in a particular column [message #189813 is a reply to message #189804] Sun, 27 August 2006 23:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Search for REPLACE() in the doco

Ross Leishman
Re: How to Change One word in a particular column [message #189821 is a reply to message #189804] Mon, 28 August 2006 00:24 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

Hi vikas,

plz find the query here

UPDATE ur_table_name
SET Narration = REPLACE(Narration, 'Vikas', 'Manish')
WHERE Narration LIKE '%Vikas%'


But try to use the indexed columns in the where clause along with this given condition.


With Regards
venkat
Re: How to Change One word in a particular column [message #189925 is a reply to message #189821] Mon, 28 August 2006 10:27 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
venkatbollu wrote on Mon, 28 August 2006 00:24

Hi vikas,

plz find the query here

UPDATE ur_table_name
SET Narration = REPLACE(Narration, 'Vikas', 'Manish')
WHERE Narration LIKE '%Vikas%'


But try to use the indexed columns in the where clause along with this given condition.


With Regards
venkat



Please be aware that the replace looks for an EXACT match. In otherwords, the replace abve would change 'Vikas' to 'Manish', but would not modify 'vikas'.
Re: How to Change One word in a particular column [message #190026 is a reply to message #189804] Tue, 29 August 2006 01:39 Go to previous messageGo to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

this is a good example for somany things, but this is not working for me. what is the reason?i connected as scott/tiger. is it the problem?
Re: How to Change One word in a particular column [message #190041 is a reply to message #189804] Tue, 29 August 2006 02:13 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

As bill said, the data you are taking from the column and the search string should be matched. I mean If you are searching on the string 'Vikas' the Database column should contain the same string 'Vikas' otherwise the statement will fail. So please make sure that you have that search value in your data.

and try this query where I modified the Where clause.

UPDATE ur_table_name
SET Narration = REPLACE(Narration, 'Vikas', 'Manish')
WHERE UPPER(Narration) LIKE UPPER('%vikas%')

Re: How to Change One word in a particular column [message #190056 is a reply to message #190041] Tue, 29 August 2006 02:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That would only select more rows; the replace is done just as often.
Re: How to Change One word in a particular column [message #190058 is a reply to message #190041] Tue, 29 August 2006 03:06 Go to previous messageGo to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

but this is showing Narration invalid identifier error
Re: How to Change One word in a particular column [message #190074 is a reply to message #189804] Tue, 29 August 2006 04:09 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

hey sun,

Narration is a column name mentioned by vikas in his query.

So you try with your own data.
Re: How to Change One word in a particular column [message #190081 is a reply to message #190074] Tue, 29 August 2006 04:20 Go to previous messageGo to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

sorry for my foolish thing Mr.venkat.

this is great function, and i successfully runned the query now


thank you very much
Re: How to Change One word in a particular column [message #190188 is a reply to message #190081] Tue, 29 August 2006 11:08 Go to previous message
vikasg2006
Messages: 78
Registered: March 2006
Location: Dubai
Member
Thanks Every body and mr venkat. Ur query is working fine .now my problem solved.
thanks again

Vikas
Previous Topic: DataPump Network Import PL/SQL
Next Topic: how to delete duplicate rows
Goto Forum:
  


Current Time: Sat Dec 10 07:12:26 CST 2016

Total time taken to generate the page: 0.05136 seconds