sforsandeep sforsandeep - 7 months ago 9
SQL Question

In MSSQL filter rows based on an ID exists in a column as comma separated string

I've Benchmarking table like this

BMID TestID BMTitle ConnectedTestID
---------------------------------------------------
1 5 My BM1 0
2 6 My BM2 5
3 7 My BM3 5,6
4 8 My BM4 10,12,8
5 9 My BM5 0
6 10 My BM6 3,6
7 5 My BM7 8,3,12,9
8 3 My BM8 7,10
9 8 My BM9 0
10 12 My BM10 9
---------------------------------------------


Explaining the table a little

Here the TestID and the connected TestID is playing the roles. If the user wants all the benchmarks for the TestID 3

It should return rows where testID=3 and also if any rows having connectedTestID column having that testID in it among the comma separated values

That means if the user specify the value 3 as the testID, it should return

---------------------------------------------
8 3 My BM8 7,10
7 5 My BM7 8,3,12,9
6 10 My BM6 3,6
--------------------------------------------


Hope its clear how those 3 rows returned. Means First row is because the testID 3 is there. the other two rows because 3 is in their connectedIDs cell

Answer

You should fix the data structure. Storing numeric ids in a comma-delimited list is a bad, bad, bad idea:

  • SQL Server doesn't have the best string manipulation functions.
  • Storing numberings as character strings is a bad idea.
  • Having undeclared foreign key relationships is a bad idea.
  • The resulting queries cannot make use of indexes.

While you are exploring what a junction table is so you can fix the problem with the data structure, you can use a query such as this:

where testid = 3 or
      ',' + ConnectedTestID + ',' like '%,3,%'
Comments