SQL SELECT LIKE containing only specific words

3
votes

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

3
sqlselectsql-like
can you elaborate on this part please "which column1 contains word1 word2 and word3 and nothing else!" – Hamed Al-Khabaz
Now I get results in which Column1 contains more than these 3 words, like 'word1 word2 word3 word4' and I need results matching the exact specified words in LIKE clause, but in any order. – Mario M
At the moment, you're matching on parts of words as well as whole words. Do you want to match 'word1word3word2' and well as 'word3 word2 word1'? – Damien_The_Unbeliever
I don't know if it's just me, but I would like an example of what the preferred output should be if possible, because I still don't understand. Thank :) – Hamed Al-Khabaz

3 Answers

4
votes

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.

0
votes

Try This

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

in MySQL you can use regexp as

SELECT * FROM mytable
WHERE column1 regexp '^word[1-3]$';

in postgres you can use 'similar to' key word

i think oracle also has regexp