Need to Remove ' Single Quote From Column Contents 2

[JohnBates]

hi again,

There are several characters I need to strip-out of a column.

The 1 giving me a problem is the single quote.

This doesn't work:
UPDATE [Dealer Names And AddressesTEST]
SET [Dlr Name] = REPLACE([Dlr Name], '[']', '')

This of course gives a syntax error:
UPDATE [Dealer Names And AddressesTEST]
SET [Dlr Name] = REPLACE([Dlr Name], ''', '')

What's the trick?

Thanks a lot! John

 

[SQLDenis]

declare @v varchar(50)
select @v ='o''brien'
select @v,replace(@v,'''','')

UPDATE [Dealer Names And AddressesTEST]
SET [Dlr Name] = REPLACE([Dlr Name], '''', '')

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[JohnBates]

The 1 thing I didn't try. That worked.

Thanks SQLDenis. I've learned a lot from you this week.

John

 

[SQLDenis]

No problem and thanks

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[resdevlabs]

Try this

UPDATE [Dealer Names And AddressesTEST]
SET [Dlr Name] = REPLACE([Dlr Name], '''', '')

That's four single quotes. SQL Books online (BOL) states that to represent an embedded quotation mark, use two single quotes.

Alternately:
SET QUOTED_IDENTIFIER OFF
UPDATE [Dealer Names And AddressesTEST]
SET [Dlr Name] = REPLACE([Dlr Name], "'", "")

Setting QUOTED_IDENTIFIER OFF allows the use of double quotes as string delimiters.


Rob
---------------------------------------------------------------
There are 10 types of people in this world.
Those who understand binary, and those who don't.