SQL SELECT LIKE containing only specific words

Question

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

can you elaborate on this part please `"which column1 contains word1 word2 and word3 and nothing else!"` — Hamed Al-Khabaz, Jan 12 '13 at 06:54
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, Jan 12 '13 at 06:55
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, Jan 12 '13 at 06:56
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, Jan 12 '13 at 06:59

Damien_The_Unbeliever · Accepted Answer · 2013-01-12T07:17:22.437

4

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.

edited Jan 12 '13 at 07:17

answered Jan 12 '13 at 07:01

Damien_The_Unbeliever

234,701
27
340
448

this query returned a result containing only word1 and word2 – Mario Jan 12 '13 at 07:08
@MarioM - I've updated the query because I realised it wasn't doing the "all of these words" portion. – Damien_The_Unbeliever Jan 12 '13 at 07:09
Now it works better, but it also returns records which does not contain word3 – Mario Jan 12 '13 at 07:13
@MarioM - it shouldn't be able to, since the third `LIKE` would be false. – Damien_The_Unbeliever Jan 12 '13 at 07:14
This is my query: SELECT * FROM MyTable WHERE Model like '%samsung%' AND Model like '%galaxy%' AND Model like '%s%' AND REPLACE(REPLACE(REPLACE(' ' + REPLACE(Model,' ',' ') + ' ', ' samsung ',''), ' galaxy ',''), ' s ','') = '' – Mario Jan 12 '13 at 07:15
and it returns 2 rows, 1 contains 'Samsung Galaxy S' and one contains 'Samsung Galaxy' – Mario Jan 12 '13 at 07:15
I don't need the second row – Mario Jan 12 '13 at 07:18
I've added a fiddle which shows it working against your original data. The issue is that `s` is a substring of `samsung` so the `LIKE`s register a false positive (as they always did, even in your original question. Give me a second to edit – Damien_The_Unbeliever Jan 12 '13 at 07:18
@MarioM - please see re-edit - this is the issue I called out in a comment to your original question - you're matching on subsequences within words. – Damien_The_Unbeliever Jan 12 '13 at 07:22
I have tried your sample on fiddle and it works, but on my database now it does not return any row. – Mario Jan 12 '13 at 07:27

score 0 · Answer 2 · answered Jan 12 '13 at 06:56

0

Try This

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

answered Jan 12 '13 at 06:56

Code Spy

9,626
4
66
46

1

With no wildcards and different expressions, no two of those `LIKE`s can ever be true, so the result is always an empty set. – Damien_The_Unbeliever Jan 12 '13 at 06:57
1

with this query, I get results which contain only one word, I need the result to return a string containing all 3 words in any order – Mario Jan 12 '13 at 06:57
1

Please see the example which I have added to question – Mario Jan 12 '13 at 07:10

score 0 · Answer 3 · edited Feb 21 '18 at 13:26

0

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

edited Feb 21 '18 at 13:26

A_Sk

4,532
3
27
51

answered Jan 12 '13 at 07:15

vidyadhar

3,118
6
22
31

i don't know SQLite but i think it will work on MS SQL, you just make a try with sample data – vidyadhar Jan 12 '13 at 07:21

SQL SELECT LIKE containing only specific words

3 Answers3

Linked