CODEWITHSUNDEEP
sqlselectsql-like
Mario
Mario

Reputation: 14800

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

Upvotes: 3

Views: 69026

Answers (3)

vidyadhar
vidyadhar

Reputation: 3178

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

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

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.

Upvotes: 4

Code Spy
Code Spy

Reputation: 9964

Try This

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

Upvotes: 0

Related Questions