Escaping Single Quotes
Have you tried the following option :
Declare @find nvarchar(5)
Declare @replace nvarchar(5)
Declare @yourvariable –this is the variable used in search
Set @find = ”+ char(39) +” — these are two single quotes
Set @replace = ”+char(39)+char(39)+”
Set @yourvariable = replace(@yourvariable,@find,@replace) –this will replace one single quote with two double quote
This will be helpful in statement such as select * from tbl_name where column_name like ‘%+@yourvariable+%’
I found this suggestion here : http://blog.sqlauthority.com/2008/02/17/sql-server-how-to-escape-single-quotes-fix-error-105-unclosed-quotation-mark-after-the-character-string/
good luck.
As I said, I’ve tried it all and I’m aware of Pinal’s solution as I consulted him and MVP Plamen Ratchev who also validated all the code I was writing (just in case I was smoking whatever I was smoking too green 🙂 ). The reason I asked is because nothing I have tried to date works and this is causing an issue with my client’s integration with people whose names contain apostrophes.
MG.-
Mariano Gomez, MVP
🙂
Was just asking.. but I thought too that you certainly had stumbled across this one… So it looks like SmartConnect doesn't deal very well with single quotes… let use know if you find the solution elsewhere… because I'm too going to run certainly into that issue, considering the various location we use data from (especially Europe).
Did either of y’all find a solution to this?
This did not work for me. At the end I had to create an script task to scape the quotes and assign the value to a custom global variable.
u replace the the single quote(‘)
with (”).
More about…….. handling single quotes
Antonio