SQL : how to check if a column value has all elements from a string array

DinoMyte 0
Viewed 45

I have a table where a column called Phrase has a sentence.

For example :

ID  |     Phrase    |
----+---------------+
 1  | I can do it   |
 2  | Where is that |

I also have another table which has keywords. For example :

select keyword 
from keywords

returns

    ID | Keywords |
    ---+----------+
     1 |    I     |
     2 |    can   |
     3 |    do    |
     4 |    it    |

How can I can check if the phrase column for a given row has all matching keywords. Something like:

select * 
from table 
where phrase like '%I%' 
  and phrase like '%do%' 
  and phrase like '%can%' 
  and phrase like '%it%'

Thanks

sql
2 Answers

It's a little bit tricky but by using String_Split it can be handled like this:


SELECT ph.Id,
       ph.Phrase
FROM
(
    SELECT Id,
           Phrase,
           value
    FROM Phrases
        CROSS APPLY STRING_SPLIT(Phrase, ' ')
) ph
    LEFT JOIN Keywords keys
        ON keys.Keyword = ph.value
GROUP BY ph.Id,
         ph.Phrase
HAVING SUM(   CASE
                  WHEN keys.Keyword IS NULL THEN
                      1
                  ELSE
                      0
              END
          ) = 0;
Saeed Esmaeelinejad0

You may try the following to get the phrase that contains all of the keywords listed in keyword table:

SELECT T.ID, T.Phrase 
FROM table_name T JOIN keyword K
ON T.Phrase LIKE CONCAT('%',K.Keywords,'%')
GROUP BY T.ID, T.Phrase
HAVING COUNT(*) = (SELECT COUNT(*) FROM keyword)

See a demo.

userGpjR0
Related