Raspi Surya Raspi Surya - 1 month ago 11
SQL Question

SQL count string matches in each row

Please take a look at this simple SQL server database :
database

What I want is, I want to create a summary with only 3 column, here is the code:

select ProductID, Name,
*code* as CountString
from product
where Name in ('this', 'is', 'count', 'example')


I want the result to have 3 column, and the column "CountString" is the total number of string that matches ('this','is', 'count', 'example'). Here is the result I want :

Result

So for example, I want the Countstring for ProductID 1 is 4, because it contains all of 4 words.

If you can solve this, it would be amazing!

Answer Source

If I understand correctly:

select ProductID, Name,
       ( (case when Name like '%this%' then 1 else 0 end) +
         (case when Name like '%is%' then 1 else 0 end) +
         (case when Name like '%count%' then 1 else 0 end) +
         (case when Name like '%example%' then 1 else 0 end)
       ) as CountString
from product;

Note: Any Name that has "this" also has "is".

If "words" are separated by spaces (and only spaces), you can do:

select ProductID, Name,
       ( (case when concat(' ', Name, ' ') like '% this %' then 1 else 0 end) +
         (case when concat(' ', Name, ' ') like '% is %' then 1 else 0 end) +
         (case when concat(' ', Name, ' ') like '% count %' then 1 else 0 end) +
         (case when concat(' ', Name, ' ') like '% example %' then 1 else 0 end)
       ) as CountString
from product;