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]%'