enter image description here

I have a table with the following texts and the key word i am searching for is 'Search'.So i have written a query

   [TextValue] LIKE '%Search%'

Q.1 How can i modify the query so that only the records having 'Search' in the text is returned and it shouldn't be taking 'LSearch'. i.e as per the image first three records only to be returned.?

  • because of Search and LSearch is some replacement parameter (I think), use: s.z NOT LIKE '%LSearch%' AND s.z LIKE '%Search%'
    – garik
    Feb 5, 2011 at 12:56
  • good question, since many of us not aware of available wildcard characters that can be used with LIKE. I'm adding this link/reference, which may help anyone who wants to know more about it. Feb 22, 2011 at 6:05
  • and one from msdn Feb 22, 2011 at 6:15

3 Answers 3


Using LIKE

  • Single line like search:

     ' ' + [TextValue] + ' ' LIKE '%[.,;:() ]Search[.,;:() ]%'
    /* [] contains list of allowable characters, adding spaces around 
       [TextValue] removes need to have multiple OR [TextValue] LIKE */
  • Without padding you will need to specifically handle string appearing at start/end of string:

        [TextValue] LIKE '%Search%' --middle
        [TextValue] LIKE 'Search%'   --start
        [TextValue] LIKE '%Search'   --end


Edit2, for the full text purists

CREATE FULLTEXT INDEX on MSDN states for the AUTO option (the default)

Although changes are propagated automatically, these changes might not be reflected immediately in the full-text index.

So, it may not give correct results. But then a few seconds later it will.

Also, for upto a few 10k rows it will perform adequately: it scales O(n). I use the LIKE on a table with around 25k rows but users know it will perform badly if they search this way (it's on an "advanced" page). I gain in the trade off by managing a full text index

Full text search isn't the correct solution, it's one option

Note: Experience has shown me that FullText indexing in SQL Server has severe performance implications, ie: Only implement if you really need to and you know what you're doing! (@Andrew Bickerton)

  • It looks fine.But is there any other way i could bring it through any wild card expressions?.Actually i am implementing it in a search query.And am trying to reduce the line of code.
    – balu
    Feb 5, 2011 at 9:56
  • I tried by adding spaces but it is not returning the first record i.e the key word at the beginning of the text.
    – balu
    Feb 5, 2011 at 10:12
  • @balu: Are you running this exact code after my edit?
    – gbn
    Feb 5, 2011 at 11:01
  • I don't like downvoters. Don't worry. If my quick answer was incorrect, I thank you for cold water. I had not time to quick response(edit) my answer.
    – garik
    Feb 5, 2011 at 16:13
  • 2
    This isn't a scalable solution. In will use an index scan or table scan. As the table grows the amount of time searching the table will grow dramatically. The full text option shown is the one which should be used.
    – mrdenny
    Feb 6, 2011 at 2:17

If your table will have a non-trivial amount of rows, you might want to try a FULLTEXT index instead. It will be much faster and will match just on the exact word.


CREATE FULLTEXT INDEX ON [dbo].[SearchLike](TextValue)
    KEY INDEX pk_id; --requires the existing of a PK or UQ index with this name on this table

Now search for your text:

   CONTAINS([TextValue], 'Search')

More on:

  • Full text search within SQL Server will be the only efficient way to search the data. Any other way that I can think of would require an index or table scan.
    – mrdenny
    Feb 6, 2011 at 2:15

I don't know if SQL server supports \b in regexes (match on word boundry). And you'll have to create a user defined function to allow regex matching in the first place, but if it does, you could match on:


If it doesn't, you should still be able to match on:

(^| )Search( |$)
  • 1
    At this time, SQL Server does not support native regex searches using LIKE. CLR is an option, but a slow one. Feb 5, 2011 at 21:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.