Replace Apostrophe in SQL

replace(lastname," ' '",'"x"') (spaces for clarity only)

Result:

Invalid column name ' ' '.

How do I get around the invalid column name?

Thanks in advance for your assistance!

Just double the apostrophy. Example, replace(lastname," ' ' '",'"x"') (spaces for clarity only)|||tried that also...

Invalid column name '''''

|||using SQL in vb6|||replace(lastname, ' ' ' ', 'x' ) (spaces for clarity only)|||

Seems like the error is most likely because you are using double quotes for a string. SQL Server has a special usage for double quotes. That is to identify object names -not enclose string values.

So if you are attempting to replace two single embedded quotes with a character [x], try this:

Code Snippet

DECLARE @.LastName varchar(20)
SET @.LastName = 'O''''Reilly'
SELECT replace( @.LastName, '''''','x')

If I have misread your intentions, please clarify what you are attempting to replace, and with what value...

|||

You can use either escape sequence (as Arnie suggested)

or

The QUOTED_IDENTIFIER settings, as follow as

Code Snippet

SETQUOTED_IDENTIFIEROFF

Selectreplace("O'Reilly","'",'"x"')

|||

If you're using VB snytax string.replace...double quotes around the "x" is sufficient. You don't want to replace the apstrophe with 'x' just x

If you can, you want to trim using vb on the form before it gets to SQL and I think that's what you want to do here.

Adamus