问题描述
我有以下情况
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