SQL Select Where Field Contains Words

SQL SELECT WHERE field contains words

Rather slow, but working method to include any of words:

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

If you need all words to be present, use this:

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

If you want something faster, you need to look into full text search, and this is very specific for each database type.

SQL select rows where field contains word from another table's fields

It does not work like that. You can use a join instead

SELECT l.* 
FROM leads l
JOIN keywords k on instr(leads.name, word) > 0 

Search a sentence including given words in impala

If words are on a specific order, say "Teo"; "Hi"; "code", you can use the following :
select * from db.table where content like "%Teo%Hi%Code%"; which will look for any string of that form.

Otherwhise, although probably less efficient, the following will work:
select * from db.table where content like "%Teo%" and content like "%Hi%" and content like "%Code%";

Tested this on both sqlite3 and a sql - web version.

Edit: I just saw it was tagged as impala. Although my solution would work there too, it is case sensitive.
The case insensitive string finder is ILIKE and not LIKE

How do I exclude rows in SQL where string does not contain two words

You want OR

.. not (name like '%ab%' and name like '%test%')

this is the same as

select
name
from example
where name not like '%ab%' OR  name not like '%test%

SQL SELECT LIKE containing only specific words

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.

---

Related Topics