+1-888-319-3663

COMMUNITY FORUM

Escaping Single Quotes

Mariano Gomez asked 8 years ago

I have had the most difficult time trying to escape single quotes in T-SQL statement formatted strings. For example, DECLARE @param1 VARCHAR(30) = ‘_SomeSourceField’; . . UPDATE someTable SET Col1 = 1 WHERE SomeCol = @param1; It so happens that the value in _SomeSourceField contains a single quote, for example, L’Italien. I have tried everything known so far to format @param1 with the proper escape sequence of single quotes to avoid the “Unclose quotation mark” error when the SQL task is ran. What am I missing? MG.- Mariano Gomez, MVP

Answers

0 Vote Up Vote Down

Best Answer

Beat Bucher answered 8 years ago

HI Mariano,
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.

Mariano Gomez replied 8 years ago

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

Beat Bucher replied 8 years ago

🙂
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).

callen replied 8 years ago

Did either of y’all find a solution to this?

0 Vote Up Vote Down

Elena Zapata answered 8 years ago

Hi,

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.

0 Vote Up Vote Down

anto answered 6 years ago

generally when u deal with apostrophe
u replace the the single quote(‘)
with (”).

More about…….. handling single quotes

Antonio

0 Vote Up Vote Down

Kamal Patel answered 5 years ago

I am not sure if this was answered. I was running into a similar issue with the apostrophe’s. I found that when I escaped the line with two apostrophe’s, it showed up in the destination table with 2 apostrophe’s. The other issue with this was that one of the fields I had apostrophe’s on was a primary key in another table and the map was setup to Update Existing records in the destination table. It looks like when the “Update” happens it validates if the record exists comparing the value with 1 apostrophe, but doesn’t find a match so tries to insert the value with 2 apostrophe’s. The way that I worked around this was that I setup a Run SQL Command task to run at the end to update the records that were inserted to now have one apostrophe.

If you would like to submit an answer or comment, please sign in to the eOne portal.