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
相关文章