CODEWITHSUNDEEP
sql-servert-sql
smpa01
smpa01

Reputation: 4282

TSQL to search text

I have a situation as 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 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           |

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

Upvotes: 2

Views: 61

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56725

Try it like this:

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

Upvotes: 1

Related Questions