LIKE to select the independent existence of the word whereever in the text

Question

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

Answer 1

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)

Answer 2

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:

• CREATE FULLTEXT CATALOG
• CREATE FULLTEXT INDEX
• CONTAINS
• LIKE

Answer 3

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( |$)