TSQL 搜索文本

问题描述

我有以下情况

declare @vendor as table (vName varchar(max))
insert into @vendor
select *
from
(
    values
        ('Maccro'),
        ('Accro')
) t (one)

declare @transaction as table (descr varchar(max))
insert into @transaction
select *
from
(
    values
        ('recl Maccro something'),
        ('lrec Accro Maccro'),
        ('lrec Maccr0'),
        ('Maccro indeed'),
        ('ACCR Accro'),
        ('Raac else')
) t (one)

我想运行一个查询，该查询将在 @transaction 的每个行值中搜索来自 @vendor 的每个值，并且该值是否包含在 中当第一个值匹配时，descr 返回 1.

I want to run a query which will search each value from @vendor in each of the row values of @transaction and if that value is contained within descr returns 1 when the first value is matched.

所以在我的例子中，我想要的结果如下

So in my example, my desired result is following

| descr                 | doesContain |
| --------------------- | ----------- |
| recl Maccro something | 1           |
| lrec Accro Maccro     | 1           |
| lrec Maccr0           | 0           |
| Maccro indeed         | 1           |
| ACCR Accro            | 1           |
| Raac else             | 0           |

我尝试通过编写 CASE 语句来做到这一点，但这意味着我需要为每个供应商手动编写该语句，这不可能，因为供应商表是动态且复合的.

I tried doing this through writing a CASE statement but that means I need to manually write that for each of the vendors which is not possible cause vendor table is dynamic and compounding.

这是我迄今为止所尝试的

This is what I have tried to so far

select 
a.descr,
CASE WHEN a.descr like '%Maccro%' then 1 else 0 end [doesContain]
from @transaction a

推荐答案

试试看:

Select 
    a.descr,
    CASE WHEN EXISTS(select * From @vendor b
                     where Charindex(b.Vname, a.descr) > 0)
              then 1 else 0 End [doesContain]
from @transaction a