Searching for strings in SQL Server using LIKE

Many times when searching for data in SQL Server we need to search for strings and use wild cards. One common wild card is %. As an example, I am going to use the Customer Master table (RM00101) in the Dynamics GP sample company, Fabrikam, Inc. If I want to all customers with ‘USA’ in the Class ID, here is a way to use %:

select * from RM00101     
where CUSTCLAS like '%USA%'

The above is equivalent to a ‘contains’ search. Anything with ‘USA’ somewhere is the Class ID will be returned.

If I want only Class IDs that start with ‘USA’, I would take the beginning % out:

select * from RM00101     
where CUSTCLAS like 'USA%'

Running the above against the Fabrikam GP company (the TWO database) will result in something like this:

You can see that there are 2 formats: USA-xxxxxx-Tx with 6 characters in the middle and USA-xxxx-Tx with 4 characters in the middle. Let’s say you wanted to filter out only those with 4 characters in the middle, excluding the ones with 6 characters from your result. How would you do that? There is another wild card you can use, the underscore. Here is what the syntax looks like:

select * from RM00101     
where CUSTCLAS like 'USA-____-T%'

That’s 4 underscores in the middle between the dashes. Try it.  One underscore will mean only one character, so you can get more precise in your searches.

Now, how would I find only those records that start with USA and have the middle section starting with IL or IN? You can certainly use the OR and just combine 2 expressions, or you could use brackets [ ], which will search for single character within the specified range:

select * from RM00101
where CUSTCLAS like 'USA-I[LN]%'

This is equivalent to finding anything starting with either ‘USA-IL’ or ‘USA-IN’.

And finally, what if I wanted to get all the records where the Class ID starts with ‘USA-I’ but excluding ‘USA-IL’ and ‘USA-IN’? I could use the [^] which will exclude a single character not within the specified range. Here is an example:

select * from RM00101
where CUSTCLAS like 'USA-I[^LN]%'

Happy searching!