Finding Apostrophes in SQL Server Text: Straight and Curly

Finding apostrophes in SQL Server text takes one trick and one trap. The trick is doubling the quote inside a LIKE pattern. The trap is that text can hold two different apostrophes, and the usual search finds only one.

The Sample Table

Eight shop names hold the marks. Five have an apostrophe, and two of those use the curly kind that word processors and phones produce. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE Shops (ShopID INT IDENTITY(1,1) PRIMARY KEY, ShopName NVARCHAR(40) NOT NULL);
INSERT INTO Shops (ShopName) VALUES
  (N'Reader''s Corner'), (N'Gardener''s Rest'), (N'Tea House'), (N'Baker Street Books'),
  (N'Ro' + NCHAR(8217) + N's Plants'), (N'Penny' + NCHAR(8217) + N's Pots'), (N'Reader''s & Gardener''s Shed'), (N'Green Door Seeds');
GO

The Quote That Ends the Pattern

A LIKE pattern sits inside single quotes. The first quote opens the string and the next one closes it. A third quote in the middle leaves a string that never closes.

SELECT ShopName FROM Shops WHERE ShopName LIKE '%'%';

SQL Server answers with Msg 105, an unclosed quotation mark, followed by Msg 102 for the bad syntax. The pattern ended after the first percent sign, and the rest of the line was read as code.

Double the Quote

Two quotes in a row inside a string stand for one real quote. The wildcards on each side let the apostrophe sit anywhere in the name.

SELECT ShopName FROM Shops WHERE ShopName LIKE '%''%' ORDER BY ShopID;

That returns Reader’s Corner, Gardener’s Rest and Reader’s & Gardener’s Shed. A set works too: the pattern %[”’]% holds the doubled quote inside square brackets and returns the same three rows.

SELECT ShopName FROM Shops WHERE ShopName LIKE '%['']%' ORDER BY ShopID;

CHARINDEX gives the same three rows without a pattern. This works because no wildcard is involved.

SELECT ShopName FROM Shops WHERE CHARINDEX('''', ShopName) > 0 ORDER BY ShopID;

NOT LIKE Finds the Others

Put NOT in front of LIKE to list the names without an apostrophe. This is where the trap shows up.

SELECT ShopName FROM Shops WHERE ShopName NOT LIKE '%''%' ORDER BY ShopID;

It returns five shops: Tea House, Baker Street Books, Ro’s Plants, Penny’s Pots and Green Door Seeds. Ro’s Plants and Penny’s Pots have an apostrophe. They have the other kind.

Straight and Curly

Straight and curly apostrophes are different characters. UNICODE returns the code of the first character in its input, and SUBSTRING hands it the mark.

SELECT ShopName,
  UNICODE(SUBSTRING(ShopName, CHARINDEX('''', ShopName), 1)) AS StraightCode,
  UNICODE(SUBSTRING(ShopName, CHARINDEX(NCHAR(8217), ShopName), 1)) AS CurlyCode
FROM Shops
WHERE ShopName LIKE '%''%' OR ShopName LIKE N'%' + NCHAR(8217) + N'%'
ORDER BY ShopID;

The straight mark is code 39. The curly mark is code 8217. A comparison confirms it: in this database, the two marks are not equal.

SELECT CASE WHEN N'''' = NCHAR(8217) THEN 'equal' ELSE 'different' END AS StraightVsCurly;

The result is different. Curly marks are common in text pasted from word processors, so a search for one kind misses rows quietly. No error appears.

Find Both Kinds

Swap the curly mark for a straight one first, then search. REPLACE does the swap inside the query and leaves the table alone.

SELECT ShopName FROM Shops
WHERE REPLACE(ShopName, NCHAR(8217), '''') LIKE '%''%'
ORDER BY ShopID;

That returns all five shops. Run the UNICODE check first when a search result looks too small.

Counting Apostrophes

To count marks in each name, compare the length before and after you remove them.

SELECT ShopName, LEN(ShopName) - LEN(REPLACE(ShopName, '''', '')) AS StraightMarks
FROM Shops
WHERE ShopName LIKE '%''%'
ORDER BY StraightMarks DESC, ShopID;

Reader’s & Gardener’s Shed has two marks, and the other two have one each. The count sees straight marks only, so wrap the name in the same REPLACE if curly ones can appear.

Watch for a Stray Space

A space is a character in a pattern too. Add one before the closing quote and the search asks for a name that ends in a space.

SELECT ShopName FROM Shops WHERE ShopName LIKE '%''% ' ORDER BY ShopID;

That returns no rows, and no error. If a pattern that should work finds nothing, look at both ends of the quoted text.

Clean Up

Remove the sample table when you finish.

DROP TABLE Shops;

What to Remember

Double the quote to put one inside a pattern. Use NOT LIKE for names without it. Check the character code when a search misses rows, because a curly apostrophe is a different character.

To go further, read How to Escape Single Quotes and Fix Error 105 and Find Rows With Single Quotes in SQL Server: SQL in Sixty Seconds #075.

An apostrophe is not one character, it is two that look the same.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.