CODEWITHSUNDEEP
sqlsql-servert-sql
Sam
Sam

Reputation: 81

How to find if a Column contains '%' in SQL Server

I have a created a table as follows:-

CREATE TABLE Customer(CustomerID integer PRIMARY KEY, Name text);

/* Added few records in this table */
INSERT INTO Customer VALUES(1,'Tom');
INSERT INTO Customer VALUES(2,'Lucy');
INSERT INTO Customer VALUES(3,'Jenny%123');

Now, I have to find the Name which contains % in them.

I should get 'Jenny%123'

The query I used is 

SELECT * FROM Customer where Name LIKE '%%%';

But I'm getting all the rows. I just want the Name that contains % in it.

Upvotes: 3

Views: 63

Answers (3)

FullMetalPanda
FullMetalPanda

Reputation: 54

SELECT * FROM Customer where Name LIKE '%[%]%'

this will work

why store % in the first place?

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use CHARINDEX:

select *
from Customer
where charindex ('%', Name) <> 0

The above is, of course, non-sargable, but it's easier to understand IMHO.

Upvotes: 6

Mureinik
Mureinik

Reputation: 311338

You could use an escape clause to treat the middle % as a literal:

SELECT * FROM Customer WHERE Name LIKE '%\%%' ESCAPE '\';
-- Use escape character -----------------^
-- Define escape character -------------------^

Upvotes: 2

Related Questions