replacing Apostrophe

I’m trying to key the syntax for replacing the Apostrophe with double
Apostrophe and update table.
Example: Programmer’s with Programmer’’s
Here code for finding occurrences:
SELECT DEALNAME
FROM DLWKTABLE20060609
where dealname like '%['']%'
BUT how do I update:
Update dlwktable20060609
Set dealname = replace(dealname, ?,?)
where dealname like '%['']%'declare @.c varchar(50)
select @.c ='O''Brian'
select replace(@.c, '''',''') ,@.c
Then the update will be
Update dlwktable20060609
Set dealname = replace(dealname, '''',''')
where dealname like '%['']%'
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Logger wrote:
> I'm trying to key the syntax for replacing the Apostrophe with double
> Apostrophe and update table.
> Example: Programmer's with Programmer''s
> Here code for finding occurrences:
> SELECT DEALNAME
> FROM DLWKTABLE20060609
> where dealname like '%['']%'
> BUT how do I update:
> Update dlwktable20060609
> Set dealname = replace(dealname, ?,?)
> where dealname like '%['']%'

Replace function: replacing an apostrophe with whatever

hey

i'm having a problem with a stored procedure, to cut a long story short i need to replace apostrophe's in my text, like so

SET @.prOtherValue = REPLACE(@.prOtherValue,''', '''')

this doesn't work tho

whats the work around

cheers!!!!

SET @.prOtherValue = REPLACE(@.prOtherValue,''', '')

|||

nar.. no good

this is the context i have it in

SET @.prOtherValue = REPLACE(@.prOtherValue,''','')

EXEC( 'UPDATE #_AllParticipantResponses SET '+@.qcTitle+' = '''+@.prOtherValue+''' WHERE participantId='+@.globalparticipantId+'')

the problem is in @.prOtherValue, sometimes i'll have the word isn't or whatever word, which contains an apostrophe

if you copy and paste the above into query analyzer, watch what hapens to the EXEC statement if you have an apostrophe where i have bolded

|||

SET @.prOtherValue=REPLACE(@.prOtherValue,'''','')

EXEC('UPDATE #_AllParticipantResponses SET '+ @.qcTitle+' = '''+ @.prOtherValue+''' WHERE participantId= '+convert(Varchar,@.globalparticipantId))

|||

hehe...

nar, you have 2 appostrophes for the second argument

in my database right, i have a column called OtherValue. in this column, i have words like isn't can't it's ( focus on the single appostrophe in each word)

when i try and pass words like isn't to my @.prOtherValue, the update statement fails because the @.prOtherValue value contains an appostrophe ( ' )

apostrophe's on t-sql have some sort of formatting value

when i try to execute the update statement, if i have an appostrophe in a word, it terminates

what i need to do isREPLACE(@.prOtherValue,''','whatever') but you can't have'''

cheers!!!

|||

Replace(prOtherValue,char(39),'|')

|||

Finally got a chance to try it out

Thanks for that

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

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