CODEWITHSUNDEEP
sqlt-sql
Peter J Quinn
Peter J Quinn

Reputation: 69

SELECT exact table records from a comma-delimited array using T-SQL

This I can't get my simple brain around. A comma-delimited array of products ordered is passed to a procedure like so:

set @array = 'productID_47, productID_4'

select productID, price from Products 
where @array like '%' + productID + '%'

Two records are returned. Great. But then if I have:

set @array = 'productID_47'

select productID, price from Products 
where @array like '%' + productID + '%'

Again, two records are returned which is NOT what I want. The product codes are fixed, sadly.

Any help would be appreciated, thanks

Upvotes: 1

Views: 153

Answers (1)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171431

I am assuming there are no commas when there is only one item in @array:

select productID, price 
from Products  
where @array = productID --only one item, can use index
    or @array like productID + ',%'  --array starts with item, can use index
    or @array like '%, ' + productID + ',%' --item is in the middle of @array, cannot use index
    or @array like '%, ' + productID --item is at the end of @array, cannot use index
    or @array like '%,' + productID

Upvotes: 2

Related Questions