SQL SELECT LIKE containing only specific words

I have this query:

SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
AND column1 LIKE '%word2%'
AND column1 LIKE '%word3%'

I need to modify this query to return records for which column1 contains word1 word2 and word3 and nothing else! no other words, just these words.

Example: searching for samsung galaxy s3 should return any combination of samsung s3 galaxy but NOT samsung galaxy s3 lte

Solution

Assuming that column1 contains space separated words, and you only want to match on whole words, something like:

SELECT * FROM
  (select ' ' + REPLACE(column1,' ','  ') + ' ' as column1 from mytable) t
WHERE
   column1 like '% word1 %' AND
   column1 like '% word2 %' AND
   column1 like '% word3 %' AND
   REPLACE(REPLACE(REPLACE(column1,
      ' word1 ',''),
      ' word2 ',''),
      ' word3 ','') = ''

Note that this construction does allow the same word to appear multiple times. It's not clear from the question whether that should be allowed. (Fiddle)

It would be a far better design if these words were stored as separate rows in a separate table that relates back to mytable. We could then use more normal SQL to satisfy this query. Your example looks like it's some kind of tagging example. Having a table storing each tag as a separate row (with an ordinal position also recorded, if required) would turn this into a simple relational division problem.

A way to count how many times a word appears in a column is the expression:

(LEN(column2) - LEN(REPLACE(column2,'word',''))/LEN('word')

but this would again revert back to matching subsequences of larger words as well as the word itself, without more work.