0

I am writing an SQL query on SQL Server and I need to replace all this special apostrophe ’ into a single quote '

My sql query is

SELECT  [code] AS Code
      ,Replace(Replace(Replace(Replace(Replace(Replace(Replace
      (Replace(Replace(Replace([name],'“','"'),'”','"'),'<= ','&le;'),'>=','&ge;'),'<','&lt;'),'>','&gt;'),CHAR(10),'<br>'),'\n',' '),CHAR(13),' '),'–','-') AS ShortDesc
      ,Replace(Replace(Replace(Replace(Replace(Replace(Replace
      (Replace(Replace(Replace([description],'“','"'),'”','"'),'<= ','&le;'),'>=','&ge;'),'<','&lt;'),'>','&gt;'),CHAR(10),'<br>'),'\n',' '),CHAR(13),' '),'–','-') AS LongDesc
      ,CASE WHEN isobsolete = 0 THEN 'NULL' ELSE 'Y' END AS Obsolete
      
FROM (
     SELECT ROW_NUMBER() OVER(PARTITION BY code ORDER BY effectivefromdate DESC) rn, * 
     FROM [CodingSuite_STG].[Codes].[Hcpcs] ) cs
     WHERE  rn=1 
     order by code asc

And the way i write the replace for this special character is

Replace( column_name, '’',''')

this is not working as the single quote interpret it as open quote and end quote.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • I think this question was closed prematurely. The smart quotes are Unicode characters so have you tried using [National Language](https://learn.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql#unicode-strings) strings with the `N` prefix? e.g.: `N'“'` and `N'”'` – AlwaysLearning May 07 '21 at 13:34
  • 1
    @AlwaysLearning, no they don't need to be (I checked this before closing as I thought so as well). [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f37531fbf4325243b3ae1d6db57891ea) But the OP is very clearly asking "why is `Replace( column_name, '’',''')` not working?" and the reason is because they have *not* escaped the single quote *inside* the literal string. – Thom A May 07 '21 at 13:35
  • 2
    Can I suggest you use proper XML unescaping, instead of trying to roll your own – Charlieface May 07 '21 at 13:45
  • A small suggestion is to use Translate ( column_name,''',''') , with translate you can replace multiple characters at once. – Bouam Dec 02 '21 at 10:57

0 Answers0