Replace an inverted comma with an apostrophe or typogropher's quote

• webskater

  SSCarpal Tunnel

  Points: 4214

  More actions

  April 10, 2016 at 4:49 am

  #315302

  As a front end developer, some years ago, I mentioned to a sql developer who I got data from that it was a nuisance whenever a string contained a single quotation mark as, if I had to pass the string to a javascript function, I had to always remember to escape it. He said 'I'll replace any single quotation marks with a proper apostrophe, or typographer's quotation mark.' Which he did - and it was great - I never had to take that into account again when passing data to a javascript function. Unfortunately, he is no longer around to ask how he did it. In Transact SQL, how can I replace a single quotation mark in a field value with an apostrophe or typographer's quotation mark - in a Select statement?

• Sean Lange

  SSC Guru

  Points: 286601

  More actions

  April 11, 2016 at 7:16 am

  #1871135

  With the replace function. https://msdn.microsoft.com/en-us/library/ms186862.aspx

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• webskater

  SSCarpal Tunnel

  Points: 4214

  More actions

  June 7, 2016 at 1:25 am

  #1883506

  Thanks for your reply. This has just come up again and I still can't get it to work.

  Say I have an email address like FredO'Shea@fred.cam

  The apostrophe in that email address is a typographer's quote that was put in there by typing Alt 0146 - it is not an ASCII character

  If that email address is set to a variable called @EmailAddress and I try to Replace it using

  REPLACE(@EmailAddress, ''', ''') - it doesn't work.

  Sorry, it is hard to show here - in the REPLACE function above it should contain, from the left ...

  The variable @EmailAddress,

  An inverted comma, a typographer's single closing quotation mark, an inverted comma,

  An inverted comma, an inverted comma, an inverted comma,

  I have tried doubling up on the single quotes - but I can't get it to replace the typographer's quotation mark with a ASCII single inverted comma.

  (The reason for this is that an Exchange Mail Server rejects an email address with a typographer's quote in it).

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 7, 2016 at 4:40 am

  #1883531

  SELECT NCHAR(8217); -- Alt 0146

  -- Replace right closing quote with single quote

  DECLARE @EmailAddress NVARCHAR(100) = 'FredO' + NCHAR(8217) + 'Shea@fred.com';

  SELECT @EmailAddress;

  SELECT REPLACE(@EmailAddress,NCHAR(8217),'''');

  -- Replace single quote with right closing quote

  SET @EmailAddress = 'FredO''Shea@fred.com';

  SELECT @EmailAddress;

  SELECT REPLACE(@EmailAddress,'''',NCHAR(8217));

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden