SQL计数字符串在每行中匹配 [英] SQL count string matches in each row

问题描述

请看一下这个简单的SQL Server数据库:

Please take a look at this simple SQL server database :

我想要的是，我想创建一个只有3列的摘要，下面是代码:

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')

我希望结果具有3列，而"CountString"列是匹配的字符串总数("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 :

例如，我希望ProductID 1的Countstring为4，因为它包含所有4个单词.

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!

推荐答案

如果我正确理解:

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;

注意:任何具有"this"的 Name 也具有"is".

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;

这篇关于SQL计数字符串在每行中匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！