Trouble with Select Statement For Records with Apostrophe

[bmann]

Hello. I am having trouble with ths SQL code in sql analyzer. I am trying to change the lastname O'NEIL to
ONEIL.


Update cal.dbo.name
set last = Replace(last , "'", "")
where last LIKE 'O''NEIL%'


This is the error I get.

Server: Msg 1038, Level 15, State 3, Line 2
Cannot use empty object or column names. Use a single space if necessary.

 

[DotNetGnat]

i dont understand what you are doing but you are missing qutoes in your replace statement

set last = Replace(last ,"'", "''")

-DNG

 

[DotNetGnat]

oh ok...i was wrong..so you want to totally replace the single quote...

-DNG

 

[bmann]

Hello DOTNETGNAT. Let me Explain.

Field Data

Last O'NEIL


I want to change Data to take the apostrophe from name:

Field Data

Last ONEIL

 

[PHV]

UPDATE cal.dbo.name
SET last = REPLACE(last , '''', ' ')
WHERE last LIKE 'O''NEIL%'


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886