Search code examples
sqloptimizationsql-likesql-in

Is there way to combine as and in SQL Server

select ptidentifier, patientname, patientage, patientcreditcards
from patients with (nolock)
where ptidentifier like '%3026737%'

Let's say I have like 20 different patient identifier, in order to an like, I need to insert one at a time, is there way to do it combining in and like? So I can search multiple patientidentifiers at same time?

I use as because the patientidentifier is not exactly like the 302673, since that's all the info that was provided to me.

Solution

  • One method is:

    where ptidentifier like '%123%' or
      ptidentifier like '%456%' or
      . . .

    Your code looks like SQL Server, so here is another method:

    select p.*
from patients p
where exists (select 1
              from (values ('123'), ('456'), . . .) v(identifier)
              where p.ptidentifier like '%' + v.identifier + '%'