March 2, 2009 
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!
Victoria Yudin 
hellu Joe B,
the way u have created the query to find an entry in a 25-column table is very naive. The style of ur query depends on whether ur table contains any column with unique values(read ‘primary key’). If it contains a primary key then u can simply search it by using like for that single column.
A table without any unique valued column/s is a very bad way to design a table. This makes it difficult to refer to any particular entry easily.
-Prithvi
LikeLike
Victoria,
What if you have up to 25 columns where you would want to use the LIKE operator… is it inefficient to create a SQL similar to the following? Is there a more efficient technique?
Thanks,
Joe
select * from RM00101
where
FIELD1 like ‘%ABC%’ and
FIELD2 like ‘BCD%’ and
FIELD3 like ‘%CDE’ and
FIELD4 like ‘%GHT%’ and
FIELD5 like ‘%QMT%’ and
FIELD6 like ‘%KUT%’ and
FIELD7 like ‘%LIU%’ and
FIELD8 like ‘%RRR%’ and
etc…
FIELD25 like ‘%GHT%’
LikeLike
Joe,
This is a great question and let me just admit upfront that I am by no means a SQL Server expert. In general, using LIKE or NOT LIKE is inefficient, especially with a preceding wildcard(%). This is right out of the SQL Server manual. However, in my experience, if your data set is not huge and your server is not terribly slow, it may very well be that even though the code is not optimal, it will perform just fine in which case it might not be worth optimizing anything to save 2 or 3 seconds.
There is no one answer I can give you to say “use _____ instead of LIKE”. If you’re really looking to get data from just the RM00101 table, I would have a hard time imagining a scenario where you would actually need 25 statements all using LIKE to get your desired result set. If that is just an example, then without more information on what specifically you are looking to do and where/how you are going to use it, it’s difficult to suggest anything more efficient.
-Victoria
LikeLike
So, I am guessing that standard Regular Expression technology is embedded in SQL? Is there documentation relevant to those Regular Expressions allowed in SQL?
LikeLike
I am in the process of learning a lot more about SQL, so I am probably not the best person to answer this just yet… But I believe this is going to differ based on your version of SQL Server, here is an online resource I found for SQL Server 2005 that may help answer your question: http://www.codeproject.com/KB/string/SqlRegEx.aspx.
-Victoria
LikeLike
great tips !
LikeLike