I got asked an interesting question recently, I was demonstrating how you can use wild cards in a WHERE clauses when someone asked ‘How do you search for a wild card character when using a LIKE based WHERE clause fro string comparisons. I must have looked stumped for a minute because my mate Jim happened to mention that I did,  add then asked ‘How I was going to ESCAPE from that’

That's how we search for a wildcard character when doing a string comparison, we escape the wild card characters. The code below shows how we do this:

--Demo

--Create table
create table t2
(id int, Name varchar(35))
go

--Populat the data with some data
declare @i int
set @i =0
while @i <= 10
begin
insert into t2
values (@i, REPLICATE('A',10))
set @i = @i + 1
end

--Add a Wild Card to one of our records

update t2
set Name = 'gethyn % Ellis'
where id = 5

We have created a table added some rows to our table and we have added some wild cards to our varchar column. Next we’ll do a string comparison looking for our % sign in the name column.

 --Search for the % sign by escaping the character
-- There are a couple of ways to this

--In this first query the [] brackets tell the database engine to treat % as a normal character and not a wildcard
select * from t2
where Name like '%[%]%' --escape '%'


--In this second query we have defined a '\' as they escape character which means the next character will be treated as literal
select * from t2
where Name
LIKE '%\%%' ESCAPE '\'

--both queries return the same results

Both queries return the same results. In the first query we used the [] brackets to tell the database engine to treat the % as a normal character and not a wild card. In the second 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

Now this example is a little contrived and probably would not be used in the real world, so where would it be used. I have seen it used in when looks for offers 25% off etc. So retailer, banks, anyone that offers a percentage discount may need to know how to search for wild card operators in a string comparision