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

CustomerID	FirstName	LastName
1	Patrick	First
2	Patrick	Second
3	Pick	Third
4	Luke	Fourth
5	Patrick	Tenth
6	Park	Last

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:

CustomerID	FirstName	LastName
1	Patrick	First
2	Patrick	Second
3	Pick	Third
5	Patrick	Tenth
6	Park	Last

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.

CustomerID	FirstName	LastName
1	Patrick	First
2	Patrick	Second
5	Patrick	Tenth

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.