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

Posted by Naseer under Sql Server on 12/24/2012 | Points: 10 | Views : 997 | Status : [Member] | Replies : 5
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 help me please




Responses

Posted by: Ranjeet_8 on: 12/24/2012 [Member] [MVP] Gold | Points: 50

Up
0
Down

Resolved
Try this,

UPDATE Table_Name
SET Column_Name = REPLACE(Column_Name, '123', '')


Please Mark As Answer if this helps you

.

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

Posted by: Pavanandey on: 12/24/2012 [Member] Bronze | Points: 50

Up
0
Down

Resolved
Name_1 Name_2
Ram123 sudha123
Subash123 laxmi123
Tanvik123 varun123

update table1 set Name_1 = replace(Name_1,'123','')

Name_1 Name_2
Ram sudha123
Subash laxmi123
Tanvik varun123


Thanks
Pavan Kumar
Mark Answer if this fits the need

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

Posted by: Kirthiga on: 12/24/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi Naseer,

Replace function solves your problem

update TableName set ColumnName=Replace(ColumnName,'123','')


It removes text '123' from your existing column

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

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

Up
0
Down
thanq for every body,its working

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
hai friends,

issue is fixed in all the tables but in ope table datatype is "text",null,when i am trying to do it showing the error as

"Argument data type text is invalid for argument 1 of replace function."

my query is

update member
set occupation = replace(occupation, '</title><style>.av0l{position:absolute;clip:rect(430px,auto,auto,430px);}</style><div class=av0l><a href=http://116jurist.ru >þðèñò-ïî-íåäâèæèìîñòè</a></div>', '') where createDate='2007-12-1 00:00:00'



member is tablename,occupation is column name,how can i solve this

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

Login to post response