Search a whole word in a column value

Question

I need to write T-SQL query that returns records in which Text column value contains the whole word he.

How can I do so?

SELECT
  CreationDate,
  Text
FROM Comments
WHERE Text LIKE '%he'
ORDER BY Id DESC

This query returns records with Text column values containing he as a part of words, e.g. the distribution.

Answer 1

Here I send my query according to what I understood about your requirement.

  SELECT O.create_date, 
         O.modify_date, 
         T.COLUMN_NAME 
    FROM Information_Schema.COLUMNS T 
            INNER JOIN sys.objects O
                    ON O.name = T.TABLE_NAME
                   AND O.type = 'U'
   WHERE T.TABLE_NAME   = 'table_name' 
     AND T.COLUMN_NAME  LIKE '%he%'

Regards.

Answer 2

Here is one option:

where ' ' + text + ' ' like '% he %'