If the contents of a database table cell is a list, how do I check if the list contains a specific value in T-SQL?

Question

I have a SQL Server database table with a column called resources. Each cell of this column contains a comma-delimited list of integers. So the table data might look like this:

Product_ID    Resources    Condition
1             12,4,253     New
2             4,98,102,99  New
3             245,88       Used
etc....

I want to return the rows where a resource ID number is contained in the resources column. This doesn't work, but something like this:

SELECT *
FROM   product_table
WHERE  resources CONTAINS 4

If this was working, it would return the rows for product_id 1 and 2 because both of the resources cells in those rows contain the value 4. It would not return product_id 3, even though the resources cell for that row has the number 4 in it, because it's not the full comma-delimited value.

What is the correct way to do this?

The correct way to do it is to normalise the data, but perhaps you don't have that option. — Andrew Leach, Mar 05 '12 at 20:55

score 1 · Answer 1 · answered Mar 05 '12 at 21:00

1

you could do like this...

select * 
from product_table
where ',' + resources + ',' like '%,4,%'

but this probably won't use an index so it'll be slow if the table is large. A better solution if possible is to normalize by having an extra table with product_id and resource_id with values like (1,12), (1,4), (1, 253), (2,4), etc. It would be much faster because it'd use indexes

answered Mar 05 '12 at 21:00

Rodolfo

4,155
23
38

1

that's why there is a ',' before and after resources – Rodolfo Mar 05 '12 at 21:03

score 1 · Answer 2 · answered Mar 05 '12 at 21:01

First of all, if you have any control over the schema, the actually correct way to do this is to have a many-to-many table of resources, so that you don't need to have a comma-separated list.

Barring that, you'd need a set of LIKE cases that are joined with OR, to deal with the different cases where the item you want is the first item, the last item, one of the middle items, or the only item.

score 1 · Accepted Answer · answered Mar 05 '12 at 21:25

Use the Split function as outlined in this resource:

CREATE FUNCTION Split
(
  @delimited nvarchar(max),
  @delimiter nvarchar(100)
) RETURNS @t TABLE
(
-- Id column can be commented out, not required for sql splitting string
  id int identity(1,1), -- I use this column for numbering splitted parts
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select 
    r.value('.','varchar(5)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END
GO

-- Create the test table and insert the test data
create table #test
(
product_id int, 
resources nvarchar(max), 
condition nvarchar(10)
);

insert into #test (product_id, resources, condition)
select 1, '12,4,253', 'new'
union
select 2, '4,98,102,99', 'new'
union 
select 3, '245,88', 'used';

-- Use the Split function and cross apply to grab the data you need

select product_id, val, condition
from #test
cross apply dbo.split(#test.resources,',') split
where val = '4' -- split returns a string so use single quotes

score 1 · Answer 4 · edited May 23 '17 at 12:12

1

Or use the split function as described here and call it like this:

select * from Products where exists (select * from dbo.Split(',', Resources) where s = '4')

edited May 23 '17 at 12:12

Community

1
1

answered Mar 05 '12 at 21:32

David Brabant

41,623
16
83
111

If the contents of a database table cell is a list, how do I check if the list contains a specific value in T-SQL?

4 Answers4