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

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

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)

StackExchange Database Administrators Q#1083, answer score: 8