Search for Wild Card Characters in a Column

First create a table


CREATE TABLE Table_1 (CustID int, CustName varchar(35))
go
--Now Populat the table with some data
DECLARE @iCtr INT
SET @iCtr =0
WHILE @iCtr <= 100
BEGIN
INSERT INTO Table_1 VALUES (@iCtr, REPLICATE('X',30))
SET @iCtr = @iCtr + 1
END
--Add a Wild Card to one records
UPDATE Table_1 SET CustName = 'Arun % Ladha' WHERE CustID = 5


Now the table is created and I had updated one record with [%] wild card character. Now I want to search all the record where this Wild Card Character is there. There are many ways to do this. I am showing two of them.

--In this first query we have defined a '\' as they escape character which means the next character will be treated as literal
SELECT * FROM Table_1 WHERE CustName LIKE '%\%%' ESCAPE '\'
--In this second query the [] brackets tell the database engine to treat % as a normal character and not a wildcard
SELECT * FROM Table_1 WHERE CustName LIKE '%[%]%'
--both queries return the same results


Both Queries return the same results. In the first query we have defined ‘/’ as the escape character which the next character following the escape character will be treated as literal and not a wild card. In the second query we used the [] brackets to tell the database engine to treat the % as a normal character and not a wild card.