How to I search for a single quote character( ' ) in a text Using LIKE predicate in SQL Server

Question

I want to search and select columns where there is a single quote (') present in the text using LIKE or CONTAINS Predicate.

consider the following text : 10011-RIO MARE EXTRA'

score 2 · Accepted Answer · answered Oct 15 '18 at 06:16

2

CREATE TABLE #TEST
( Test_Column  VARCHAR(MAX));

INSERT INTO #TEST VALUES
('10011-RIO MARE EXTRA''')

SELECT * 
FROM #TEST
WHERE Test_Column LIKE '%''%'

The escape character for ' is ' used twice -> ''.

answered Oct 15 '18 at 06:16

Andrzej M.

92
3

score 1 · Answer 2 · answered Oct 15 '18 at 05:58

1

try this

select * from yourtable where youcolumn like '%''%'

answered Oct 15 '18 at 05:58

Esperento57

16,521
3
39
45

1

Can you please tell me how this actually works. – Akhil George Oct 15 '18 at 06:05

score 1 · Answer 3 · edited Oct 16 '18 at 12:58

1

Find records with single quotes in the table

SELECT *
FROM [tableName]
where Name like '%''%'

edited Oct 16 '18 at 12:58

WhatsThePoint

3,395
8
31
53

answered Oct 15 '18 at 06:10

Manish Kumar

201
2
2

score -1 · Answer 4 · answered Oct 15 '18 at 06:09

-1

I hope this solves your problem

% ' % -> Finds any values that have " ' " in any position

By executing below query you will get the result which have '(single quote) in them

SELECT * FROM TABLE_NAME WHERE COLUMN_NAME LIKE "%'%"

i have executed the same query enter image description here

answered Oct 15 '18 at 06:09

Shardul Singh Gurjar

239
3
1

I am getting an error when I run your query in SQL server. – Akhil George Oct 15 '18 at 06:41
Error : Msg 207, Level 16, State 1, Line 6 Invalid column name '%'%'. – Akhil George Oct 15 '18 at 06:42
in your query try using "%'%" instead of '%'%' you are using single quote i guess may be that is the problem – Shardul Singh Gurjar Oct 15 '18 at 07:14
this is just wrong, double quotes cannot be used in sql server – WhatsThePoint Oct 15 '18 at 08:38
it is working in mine – Shardul Singh Gurjar Oct 15 '18 at 09:09
@ShardulSinghGurjar your screenshot doesnt look like sql server, what tool are you using? – WhatsThePoint Oct 15 '18 at 09:17
@WhatsThePoint I m using adminer extension... i have tried in sql server as well it is working fine for me – Shardul Singh Gurjar Oct 15 '18 at 10:25
1

@ShardulSinghGurjar as you have mentioned you use adminer, i suspect you are using mysql and not sql server – WhatsThePoint Oct 15 '18 at 10:37

How to I search for a single quote character( ' ) in a text Using LIKE predicate in SQL Server

4 Answers4