SQL exact matching for words in a column

Question

I'm trying to find all records that have an exact match to part of the word that I'm searching for.

For example if I was looking for all the records with the word spire in it, I don't want to retrieve the rows with aspire or inspire.

SELECT  *
FROM    Table
WHERE   name LIKE '%SearchedWord%'

Answer 1

You need to split words from the name column, then an exact matching is needed.

Following SplitWords function is needed to be created. It gets a sentence, and returns words in it:

CREATE FUNCTION SplitWords(@text varchar(8000))
   RETURNS @words TABLE (
      pos smallint primary key,
      value varchar(8000)
   )
AS
BEGIN
   DECLARE
      @pos smallint,
      @i smallint,
      @j smallint,
      @s varchar(8000)

   SET @pos = 1
   WHILE @pos <= LEN(@text) 
   BEGIN 
      SET @i = CHARINDEX(' ', @text, @pos)
      SET @j = CHARINDEX(',', @text, @pos)
      IF @i > 0 OR @j > 0
      BEGIN
         IF @i = 0 OR (@j > 0 AND @j < @i)
            SET @i = @j

         IF @i > @pos
         BEGIN
            -- @i now holds the earliest delimiter in the string
            SET @s = SUBSTRING(@text, @pos, @i - @pos)

            INSERT INTO @words
            VALUES (@pos, @s)
         END 
         SET @pos = @i + 1

         WHILE @pos < LEN(@text) 
            AND SUBSTRING(@text, @pos, 1) IN (' ', ',')
            SET @pos = @pos + 1 
      END 
      ELSE 
      BEGIN 
         INSERT INTO @words 
         VALUES (@pos, SUBSTRING(@text, @pos, LEN(@text) - @pos + 1))

         SET @pos = LEN(@text) + 1 
      END 
   END 
   RETURN 
END

Then do the following SELECT statement for exact matching of the words used in the name column:

SELECT  *
FROM    [Table]
WHERE   'SearchedWord' IN (SELECT value FROM dbo.SplitWords(name))