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