Blog Post

Wildcard – Match One Character using Underscore (_)

,

I was recently contacted by a developer with what I would consider to be a very simple question.  How do you search for string of the same length starting and ending with given characters.  My response was simple, use the underscore(_) Wildcard Character Match.  The person looked very puzzled from my response.  As a result, I wrote a quick example to clarify.  For some of you this may be second nature, but for many developers and some DBAs that I have spoken to, this was a new concept.  Therefore, I am sharing this information.

Scenario:  Suppose you have the following table

CustomerIDFirstNameLastName
1PatrickFirst
2PatrickSecond
3PickThird
4LukeFourth
5PatrickTenth
6ParkLast

and you wanted to return all the customers whose name started with P and ended with a K.  One last thing, you only wanted customers whose first name contained Seven characters.  How would you accomplish this task.  If you used the following query:

SELECT * FROM Customers WHERE FirstName LIKE ‘P%k’

your results set would return the following rows:

CustomerIDFirstNameLastName
1PatrickFirst
2PatrickSecond
3PickThird
5PatrickTenth
6ParkLast

This is because the % wild card character matches any string of characters.  If you modified the query to look like this:

SELECT * FROM Customers WHERE FirstName LIKE ‘P_____k’

then you result would be correct, returning only the rows.

CustomerIDFirstNameLastName
1PatrickFirst
2PatrickSecond
5PatrickTenth

This is because I included 5 underscores as part of the character string.  These 5 underscores restricts the search to that number plus any additional characters that are specified within the character string.  In my case 2.  Therefore, my keyword search was limited to character strings that started with P, ended with K and strings that contained seven characters.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating