IN List Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

IN List Query

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2009-06-30 : 11:06:53

This works:

SELECT DISTINCT PartNumber
FROM Table1
WHERE
 (TestData IN 
  ("PASSED AT POINT1",
   "PASSED AT POINT2",
   "ACCEPTED BY BILL",
   "ACCEPTED BY MARK")
 )

What I'd like to get is something like the following:

SELECT DISTINCT PartNumber
FROM Table1
WHERE
 (TestData Like 
  ("PASSED%",
   "ACCEPTED%")
 )

Obviously, there are many more TestData parameters to include or I could just replace it with

WHERE (TestData Like "PASSED%") OR (TestData Like "ACCEPTED%")

Is there a way to do this?

Avoid Sears Home Improvement

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-30 : 11:11:51


declare @parameters table
(
    parameters varchar(100)
)

insert into @parameters
select 'PASSED'   union all
select 'ACCEPTED'

select distinct t.PartNumber
from   Table1 t
       inner join @parameters p on t.TestData like p.parameters + '%'

KH
[spoiler]Time is always against us[/spoiler]