how to delete particular text from all columns in a table [Resolved]

Posted by Naseer under Sql Server on 12/25/2012 | Points: 10 | Views : 532 | Status : [Member] | Replies : 2
hai,

In a table,i want to delete particular text from every column how can i do that ,in my table i have 17000 records,

ex:
1.naseer123
2.kiran123
3.hari123
4.ramesh123

i want to delete this "123" from all the columns,

i used query

update member
set occupation = replace(occupation, '123', '') where createDate='2007-12-1 00:00:00'

when i excecuted it showing error as "Argument data type text is invalid for argument 1 of replace function."

Datatype is "text" null, above member is tablename,occupation is column name,how can i solve this help me.




Responses

Posted by: Sinisreedhar on: 12/25/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved

update member
set column = CAST(REPLACE(CAST(column as NVarchar(max)),'123','') AS NText) where createDate='2007-12-1 00:00:00'

Ready to accept challenges at any time.....

Naseer, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Naseer on: 12/25/2012 [Member] Starter | Points: 25

Up
0
Down
thanq Sinisreedhar,its working...

Naseer, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response