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:

sqlsearch01

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!

7 Responses to “Searching for strings in SQL Server using LIKE”

  1. 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

    Like

  2. 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%’

    Like

    • 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

      Like

  3. So, I am guessing that standard Regular Expression technology is embedded in SQL? Is there documentation relevant to those Regular Expressions allowed in SQL?

    Like

  4. great tips !

    Like

Trackbacks/Pingbacks

  1. Searching on SQL Server using the LIKE command - Developing for Dynamics GP - March 4, 2009

    […] a look at Searching for strings in SQL Server using LIKE for more […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: