How can i get the records that contain a single quotation mark in SQL Server?

Question

The following as you guess, is not valid, but you get the point

SELECT [Id], [Name] FROM [People] WHERE Name LIKE '%'%'

score 1 · Accepted Answer · answered Nov 02 '17 at 19:08

1

The double single quote is interpreted as a single quote in a string:

SELECT [Id], [Name] FROM [People] WHERE Name LIKE '%''%'

answered Nov 02 '17 at 19:08

Gordon Linoff

score 0 · Answer 2 · answered Nov 02 '17 at 19:08

0

You need to escape the quote

SELECT [Id], [Name] FROM [People] WHERE Name LIKE '%''%'

answered Nov 02 '17 at 19:08

Valerica

score 0 · Answer 3 · answered Nov 02 '17 at 19:13

0

In your query, the % are under 3 ' (like '%'%'). They should be under 4.

SELECT [Id], [Name] FROM [People] WHERE Name LIKE '%''%'

The % are now escaped in this query.

answered Nov 02 '17 at 19:13

score 0 · Answer 4 · answered Nov 02 '17 at 19:23

0

You can do the escape code as others have mentioned or you can use the char code 39, where CHAR(39) is '.

SELECT [Id], [Name] FROM [People] WHERE Name LIKE '%' + CHAR(39) + '%'

answered Nov 02 '17 at 19:23

Zorkolot

4 Answers4