Wednesday, 27 July 2011

Searching for Wild Card Characters

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:


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

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

--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

No comments:

Post a Comment

Featured post

OUT OF SUPPORT: SQL Server 2008 and SQL Server 2008 R2

Welcome to this post, on SQL Server 2008 and 2008 R2, both of these versions of SQL server will go out of  extended support with Microsoft t...