Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Find Text in the Text data type with the SQL Server LIKE Operator


By:   |   Read Comments (2)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > Scripts

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem
One problem that you may be faced with is the need to find text data that is contained in a larger set of text.  There are two ways this can be done either using the LIKE operator or by using Full Text indexing.  Let's take a look at some of the options of using the LIKE operator and some pros and cons of this approach.

Solution
As mentioned already one approach of finding text that is stored within a larger set of text is by using the LIKE operator.  The LIKE operator can be used in several different manners and there are some pros and cons of using this approach to limit data when issuing your queries.  Let's first take a look at some of the options for using the LIKE operator.

Wildcard character Description Example
% Any string of zero or more characters. WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.
_ (underscore) Any single character. WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ] Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on.
[^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]). WHERE au_lname LIKE 'de[^l]%' all author last names starting with de and where the following letter is not l.

Source: SQL Server 2005 Books Online

Here are some sample queries, sample output and the execution plans that SQL Server uses to find the data.  Without an index on the LastName column all of the execution plans would perform a Clustered Index Scan.  To better show how the LIKE operator can affect performance, an index was created on the LastName column, so the results shown are how SQL Server will handle each of these queries based on having this index.


Example 1:
This query will pull back any record that starts with "Bal".  Since we have a definitive starting point "Bal", SQL Server is able to do an "Index Seek" on the table and therefore use the index on the LastName column. The overall cost for the query is 0.0082582.

SELECT * FROM Person.Contact WHERE LastName LIKE 'Bal%'

Example 2:
This query will pull back any record that contains "ale".  Since there is not a definitive starting point, SQL Server can not use the index and therefore chooses to do a "Clustered Index Scan". The overall cost for the query is 0.44451 which is not as good as the previous query.

SELECT * FROM Person.Contact WHERE LastName LIKE '%ale%'

Example 3:
This query will pull back any record that contains "ale", but also only has one character before "ale" and one character after the "ale".  Since there is not a definitive starting point, SQL Server has to scan the index.  In this example it is faster for SQL Server to scan the index created on the LastName column, but it still needs to scan the entire index.  The overall cost for the query is 0.0877988 which is not as good as example 1, but it is faster than example 2.

SELECT * FROM Person.Contact WHERE LastName LIKE '_ale_'

Example 4:
This query will pull back any record that starts with "A, B or C", contains "al" and it doesn't matter what comes after the "al".  Since there is a definitive starting point either "A, B or C", SQL Server is able to do an index seek on the LastName index. The overall cost for the query is 0.0082582 which is the same as example 1.

SELECT * FROM Person.Contact WHERE LastName LIKE '[A-C]al%'

Example 5:
This query will pull back any record that does not start with "B", but contains "ala" and any characters after that.  Since there are so many other records that SQL Server needs to evaluate it will do an Index Scan on the new LastName index.  The overall cost for the query is 0.14392.

SELECT * FROM Person.Contact WHERE LastName LIKE '[^B]ala%'


Summary
From the above samples you can see when SQL Server uses indexes and when SQL Server does not use indexes.  On small tables this is not a big deal, but if you have very large tables the LIKE operator could significantly hurt performance and therefore you should look at using Full Text Indexes instead.

Another thing to note is that you can mix and match these wildcard operators when you build your query as shown in examples 4 and 5, so you can create some complex matching strings, but again the larger the dataset the longer these operations may take.

With any function that is used in your WHERE clause there may be the possibility that SQL Server does an Index Scan vs. an Index Seek.  Keep this in mind as you develop your queries and also take a look at the execution plans to see how SQL Server will interpret and execute the query prior to pushing the code to your production servers.

Next Steps

  • The LIKE operator can be handy tool, but be aware of possible performance issues
  • Take a look at using Full Text Indexing vs. using the LIKE operator especially on larger tables.
  • Understand how SQL Server will execute your queries ahead of time, before experiencing performance issues in your production systems.


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, October 29, 2013 - 8:23:16 AM - Greg Robidoux Back To Top

Sarath, not sure I follow you example. 

Are you saying that your data in the table is stored like this (including the single quotes) 'john','kin%g'

If so, to find what you are looking for try to run a query like this:

select * from employee where name like '%john%'


Tuesday, October 29, 2013 - 2:56:08 AM - sarath gangisetty Back To Top

 

Hi Greg,

 

I am working on some search query , in that search i need to search the employee, who has the special characters as employee name.

It should be some thing mixed with the like keyword

Ex:

need to serach : 'john','kin%g'

select * from employee where employee name like ''john''

i tried to use quote name, but didn't get the result set, so please shre if you have any good suggestions

 


Learn more about SQL Server tools