Apostrophes

Finding apostrophes in (n)varchar columns

Have you ever tried to find apostrophes in your data? Looking for that row of data that breaks your JavaScript? Look no further, try the solution below!

Let’s say you query the Person.Address table in the AdventureWorks database for an address and display it on a web page. The web page implements JavaScript to handle the selected address. You notice that JavaScript errors occur only if the user selects an address that contains an apostrophe.

At first you try to find the culprit by escaping the apostrophe character in the address:

SELECT AddressLine1
FROM   Person.Address
WHERE  AddressLine1 LIKE '%'''%'

This doesn’t work; you need another way of escaping the apostrophe you are looking for. That’s where the ESCAPE keyword after the LIKE statement comes in:

SELECT AddressLine1
FROM   Person.Address
WHERE  AddressLine1 LIKE '%!''%' ESCAPE '!'


This will give you the results you were looking for.

Essentially, you are telling the parser to escape the character that follows the character you specified after the ESCAPE keyword. In this case, I'm using the exclamation mark to designate the next character to escape. I included the percent signs (%) because I don't know the exact position of the apostrophe in the address.

Next you need a function to replace the apostrophes so your code doesn't break:

CREATE FUNCTION [dbo].[sys_RemoveApostropheFromText]
(
   @txt VARCHAR(250)
)
RETURNS VARCHAR(250)
AS
BEGIN
   DECLARE @i INT
   DECLARE @ret VARCHAR(250)
 
   SELECT @i = 1, @ret = ''
   WHILE (@i <= LEN(@txt))
   SELECT @ret = @ret +
      CASE WHEN SUBSTRING(@txt,@i,1) = '''' THEN ''
      ELSE SUBSTRING(@txt,@i,1) END,
      @i = @i + 1

   RETURN

@ret

END

View the results side-by-side by including the function in your query:

SELECT AddressLine1, dbo.sys_RemoveApostropheFromText(AddressLine1)
FROM   Person.Address
WHERE  AddressLine1 LIKE '%!''%' ESCAPE '!'

The final step is creating an UPDATE statement that removes the apostrophe altogether:

UPDATE Person.Address
SET    AddressLine1 = dbo.sys_RemoveApostropheFromText(AddressLine1)
WHERE  AddressLine1 LIKE '%!''%' ESCAPE '!'

There you have it!