Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

Comments

Posted by Steve Jones on 21 October 2009

good reminder. I've rarely used the underscore, and it took me a minute to remember what it matches. I do wish the REGEX was more integrated into t-SQL. Would probably help lots of DBAs be more skilled with managing sets and thinking through patterns.

Posted by Anonymous on 22 October 2009

Pingback from  Dew Drop – October 22, 2009 | Alvin Ashcraft's Morning Dew

Posted by rudy komacsar on 26 October 2009

Also do not forget about the ESCAPE clause. Try searching for a string with an embedded or better yet, a leading underscore and you'll see what I mean. As for regexp, well, whilst not as rich as *NIX, you can come quite a long way with what has been already defined within Transact SQL

Leave a Comment

Please register or log in to leave a comment.