Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

SELECT [ID]
  ,[TextValue]
FROM
   [dbo].[SearchLike]
WHERE
   [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.?

share|improve this question
    
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 '11 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. –  Coder Hawk Feb 22 '11 at 6:05
    
and one from msdn –  Coder Hawk Feb 22 '11 at 6:15
add comment

3 Answers

up vote 7 down vote accepted

Using LIKE

  • Single line like search:

    WHERE
     ' ' + [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:

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

Using FULLTEXT INDEX

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)

share|improve this answer
    
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 '11 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 '11 at 10:12
    
@balu: Are you running this exact code after my edit? –  gbn Feb 5 '11 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 '11 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 '11 at 2:17
show 3 more comments

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 CATALOG <catalog_name> AS DEFAULT;

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:

SELECT [ID]
  ,[TextValue]
FROM
   [dbo].[SearchLike]
WHERE
   CONTAINS([TextValue], 'Search')

More on:

share|improve this answer
    
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 '11 at 2:15
add comment

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:

\bSearch\b

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

(^| )Search( |$)
share|improve this answer
1  
At this time, SQL Server does not support native regex searches using LIKE. CLR is an option, but a slow one. –  Eric Humphrey - lotsahelp Feb 5 '11 at 21:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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