Wildcard Searching

Searching for data with a less-than-desirable amount of instruction, or a vague “it ends in smith” is never fun.

However, there are of course a variety of tools that are available to the developers in the world of T-SQL.

As a self taught developer, a seemingly long time ago I found out about the % wildcard.

For example:

SELECT * FROM Person WHERE LastName LIKE '%smith'

returns every record that has a last name that ends in smith.

Another useful bit of syntax that I picked up along the way uses square brackets.

For example:

SELECT * FROM Employee WHERE SSN LIKE '[0-9]34%'

returns a list of employees who’s SSN’s start with “uh, something-three-four” (actual instructions provided to me recently).

Very recently, I was typing an instruction in and tried to find an SSN that was 334-% and ended up with a happy accident caused by typing too fast.

My code that I executed was:

SELECT * FROM Employee WHERE SSN LIKE '334-_%'

and I didn’t get an error. I expected a wacky result, but it turns out the underscore character is also a wildcard. So, my query above where I was trying to find “something-three-four” could have been written as:

SELECT * FROM Employee WHERE SSN LIKE '_34%'

and I would have gotten the same result.

I’m putting this out there as a helpful tip to any other self-taught DBA that never knew about the helpful underscore wildcard. If anyone else has any text manipulation / search tricks, feel free to leave a comment.

This entry was posted in SQL Server Development and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *