REPLACE statement - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

REPLACE statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-22 : 17:07:52

Bryan writes "I need to identify and replace some bad data in one of our tables with a couple of REPLACE statements, but SQL only seems to execute the first one, not both. Here's what I have written:

DECLARE
@find1 varchar(30),
@find2 varchar(30)
SET @find1 = '%#8217%'
SET @find2 = '%#8211%'
SELECT replace(notetext,'#8217;','') as FIND1, replace(notetext,'%#8211;%','') as FIND2
FROM tblNotes
WHERE notetext like @find1 or notetext like @find2

Once you guys figure what I'm doing wrong, how do I insert the results from this query into the actual table so the data gets changed? This only shows me what it would be like if it was inserted into the table.

fyi - the #8211; is what sql inputs from a long dash from a MS word template " – " and the #8217; is an apostrophy from an MS Word template " ’ " as well."

chadmat
The Chadinator

1974 Posts

Posted - 2002-01-22 : 17:54:07

I don't think you can do this with one query.

You will need to do 2 seperate queries like:

DECLARE
@find1 varchar(30)
SET @find1 = '%#8217%'
update tblNotes
set notetext = replace(notetext,'#8217;','')
WHERE notetext like @find1

go

Then run a second like:

DECLARE
@find2 varchar(30)
SET @find2 = '%#8211%'
update tblNotes
set notetext = replace(notetext,'%#8211;%','')
WHERE notetext like @find2

go

That should work

-Chad

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-22 : 18:00:25

You can do it in one if you really want -
You should be able to do this (unless I've missed something - haven't tried it)

DECLARE
@find1 varchar(30)
SET @find1 = '%#8217%'
@find2 varchar(30)
SET @find2 = '%#8211%'

update tblNotes
set notetext =
case
when notetext like @find1 then replace(notetext,'#8217;','')
when notetext like @find2 then replace(notetext,'%#8211;%','')
else notetext end

But chadmat's answer will probably run quicker (especially if your table is large....)

Edited by - rrb on 01/22/2002 18:01:42

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-22 : 18:22:30

You could also nest the replace functions and use one UPDATE statement:

UPDATE tblNotes
SET notetext = replace( replace(notetext, '#8211;' ,'' ), '#8217;', '')
WHERE notetext Like '%#821[17]%'

See if that works (it may need tweaking)

Edited by - robvolk on 01/22/2002 18:23:07

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources