TSQL to return what exists

user_11156131 0
Viewed 72

This is a follow-up question to this TSQL to search text

My situation is following

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)

I can run the following query

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

which returns the following

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

But is it possible to see on what value it returns 1 for doesContain, i.e. on what value from @vendor table, Exists returns a match.

My desired output is following

| descr                 | doesContain | containsWhat |
| --------------------- | ----------- | ------------ |
| recl Maccro something | 1           | Maccro       |
| lrec Accro Maccro     | 1           | Accro        |
| lrec Maccr0           | 0           |              |
| Maccro indeed         | 1           | Maccro       |
| ACCR Accro            | 1           | Accro        |
| Raac else             | 0           |              |
window-functions join tsql sql-server sql
3 Answers

Use a LEFT join of @transaction to @vendor and FIRST_VALUE() window function to get the 1st occurrence:

Select distinct
    a.descr,
    CASE WHEN b.Vname is not null then 1 else 0 End [doesContain],
    first_value(b.Vname) over (partition by a.descr order by Charindex(b.Vname, a.descr)) containsWhat 
from @transaction a left join @vendor b
on Charindex(b.Vname, a.descr) > 0

See the demo.

user_104988280

If you expect to get list of vendors for a transaction you may use outer apply with string_agg function.

Select 
a.descr,
Case When v.list Is Not Null Then 1 Else 0 End,
v.list
From @transaction a Outer Apply
    (Select String_AGG(b.vName,', ') As list From @vendor b
     Where PatIndex(Concat('% ',b.Vname,' %'), a.descr) > 0 Or
           PatIndex(Concat(b.Vname,' %'), a.descr) > 0 Or
           PatIndex(Concat('% ',b.Vname), a.descr) > 0) v
user_117698590

use a cross product between vendor and transaction then use charindex to see if the vendorname is in the descr. if you have multiple vendors this approach work well.

declare @vendor as table (vName varchar(max))
insert into @vendor
select distinct *
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)

select trans.descr,vend.vName vName,
case when charindex(vend.vName,trans.descr)>0 then 1 else 0 end doesContain
from @transaction trans
cross apply @vendor vend
order by descr,vName

output

ACCR Accro  Accro   1
ACCR Accro  Maccro  0
Maccro indeed   Accro   0
Maccro indeed   Maccro  1
Raac else   Accro   0
Raac else   Maccro  0
lrec Accro Maccro   Accro   1
lrec Accro Maccro   Maccro  1
lrec Maccr0 Accro   0
lrec Maccr0 Maccro  0
recl Maccro something   Accro   0
recl Maccro something   Maccro  1
user_40011770
Related