Update query replace all single quotes with null

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update query replace all single quotes with null

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2008-10-28 : 15:34:20

I have a table with rows which has single quotes, for this reason i am getting lot of prog errors on the front end.

Would like to replace all rows which has single quotes in it.

Update MyTable set order_info (replace with Nulls or Space) when order_info like '%''%'

Thank you very much for the helpful info.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 15:38:03

I suggest you use built in ENCODE function in the front end. Try not to update data.
But here is the way to do it if this is what you really really want..

replace(ORDER_INFO,'''',space(1))

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-28 : 15:43:27

Not sure why you would be getting programming erros on the front end because of single quotes in your data. What is your frontend? Are you using parameterized procedures or are you trying to construct in-line sql code and pass that to your database? If it is the latter I think you should ESCAPE the single quotes in your frontend rather than change the persisted data. I mean if you have a column of LastNames which could contain O'Brien, you wouldn't want to change that to OBrien would you?

Be One with the Optimizer
TG

reddymade
Posting Yak Master

165 Posts

Posted - 2008-10-28 : 15:48:49

Hello TG,

To fix it temporarily, would like to replace all single quotes with a space.

And later once i fix my front end will put it back.

I need to format the string on the front end which i will do.

Can you please tell me howm to replace using single quotes with space in my update statement.

Thank you.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 15:51:50

Read my post??

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-10-28 : 15:58:08

hanbingl gave you the REPLACE syntax but I still think that would be a big mistake. When you "put it back" how will you know what spaces need to be a quote and what was actually a space? You could do the replaces on your SELECT statements instead. like this:

select replace(lastname, '''', ' ')
from <yourTable>
Where ....

That way your front end doesn't know that the sinlge quotes were in there there.

But if you want to screw up your data this is how you could do it:

update yt set
LastName = replace(LastName, '''', ' ')
from <yourTable> yt
Where charindex('''', LastName) > 0

Be One with the Optimizer
TG

reddymade
Posting Yak Master

165 Posts

Posted - 2008-10-28 : 16:10:34

This is a Static table it does'nt grow.

total rows remain same, i do have a backup of this table.
once i have the function on the fron end will replace all rows back.

Thanks Again.

quote:
Originally posted by TG

hanbingl gave you the REPLACE syntax but I still think that would be a big mistake. When you "put it back" how will you know what spaces need to be a quote and what was actually a space? You could do the replaces on your SELECT statements instead. like this:

select replace(lastname, '''', ' ')
from <yourTable>
Where ....

That way your front end doesn't know that the sinlge quotes were in there there.

But if you want to screw up your data this is how you could do it:

update yt set
LastName = replace(LastName, '''', ' ')
from <yourTable> yt
Where charindex('''', LastName) > 0

Be One with the Optimizer
TG

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 16:14:04

Here, lets get you out of this messy situation:


ALTER TABLE <YOURTABLE> add _ORDER_INFO VARCHAR(...) --MATCH datatype of Order_info column...

UPDATE <YOURTABLE> 
SET _ORDER_INFO = ORDER_INFO, 
ORDER_INFO = REPLACE(ORDER_INFO,'''',' ')

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-28 : 16:15:16

quote:
Originally posted by reddymade

This is a Static table it does'nt grow.

total rows remain same, i do have a backup of this table.
once i have the function on the fron end will replace all rows back.

Thanks Again.

quote:
Originally posted by TG

hanbingl gave you the REPLACE syntax but I still think that would be a big mistake. When you "put it back" how will you know what spaces need to be a quote and what was actually a space? You could do the replaces on your SELECT statements instead. like this:

select replace(lastname, '''', ' ')
from <yourTable>
Where ....

That way your front end doesn't know that the sinlge quotes were in there there.

But if you want to screw up your data this is how you could do it:

update yt set
LastName = replace(LastName, '''', ' ')
from <yourTable> yt
Where charindex('''', LastName) > 0

Be One with the Optimizer
TG

should work.. and make sure you do that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources