title: TSQL to search text
body: <p>I have a situation as following</p>
  <pre><code>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)
  </code></pre>
  <p>I want to run a query which will search each value from <code>@vendor</code> in each of the row values of <code>@transaction</code> and if that value is contained within <code>descr</code> returns 1 when the first value is matched.</p>
  <p>So in my example, my desired result is following</p>
  <pre><code>| descr                 | doesContain |
  | --------------------- | ----------- |
  | recl Maccro something | 1           |
  | lrec Accro Maccro     | 1           |
  | lrec Maccr0           | 0           |
  | Maccro indeed         | 1           |
  | ACCR Accro            | 1           |
  | Raac else             | 0           |
  </code></pre>
  <p>I tried doing this through writing a <code>CASE</code> 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.</p>
  <p>This is what I have tried to so far</p>
  <pre><code>select 
  a.descr,
  CASE WHEN a.descr like '%Maccro%' then 1 else 0 end [doesContain]
  from @transaction a
  </code></pre>