How to check if a list of comma separated string values match an integer?

Question

Below I've added the SQL query.

I wanted retrieve the list of records that match a condition. I pass integer values into @ClassID and @SectionID parameters, The problem is ce.Class_ID and ce.Section_ID are lists of comma-separated string values.

SELECT ce.ID AS CircularEntryCount
FROM dbo.CircularEntry ce
WHERE ce.AcademicYearID = 1 
  AND (ce.Circular_Date = @CurrentDate OR CAST(ce.Created_Date AS date) = @CurrentDate)
  AND (ce.CircularApplicableForID = 1 OR ce.CircularApplicableForID = 3)
  AND (ce.Class_ID = @ClassID OR ce.Class_ID = '0') 
  AND (ce.Section_ID = @SectionID OR ce.Section_ID = '0')

PS: I used split string function to split the values into individual columns and compared the same with the parameters, but it shows.

Error converting data type nvarchar to bigint

(     
      @List nvarchar(2000),
      @SplitOn nvarchar(1)
)
RETURNS @RtnValue table (
      Id int identity(1,1),
      Value nvarchar(100)
)
AS
BEGIN
      While (Charindex(@SplitOn,@List)>0)
      Begin 
            Insert Into @RtnValue (value)
            Select 
                  Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
            Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
      End 

      Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))

    Return
EN

Fix your data model, so you are not storing lists of integers in strings. This is just the wrong way to store data. — Gordon Linoff, Jul 07 '19 at 13:11
Yeah you're right. I'll pass that to my team mates. @GordonLinoff — Sase, Jul 07 '19 at 13:23
It says 'TRY_CAST' is not a recognized built-in function name. @Sami — Sase, Jul 07 '19 at 13:35
According to _"I used split string function"_ I thought you're using 2012+ version, and 2008 is out of support now. — Ilyes, Jul 07 '19 at 13:41
Yeah I'm using MSSQL 2012, the function I used is a user defined one. @Sami — Sase, Jul 07 '19 at 13:44
@Sase Show the query you tried when splitting your strings. Don't make this more difficult by not providing sufficient information. You should read Erland's discussion of [arrays and lists](http://www.sommarskog.se/arrays-in-sql.html#crackthelist) first to understand and learn how to approach this problem. — SMor, Jul 07 '19 at 14:31

score 0 · Accepted Answer · answered Jul 07 '19 at 16:46

The correct solution is to fix the problem - which means changing the structure of the database to not store delimited strings at all, but instead normalize the data and use foreign keys.
For more information, read Is storing a delimited list in a database column really that bad?, and not only the accepted answer by Bill Karwin, but other answers as well.

In case you can't change the database structure, you can use a workaround using like:

SELECT ce.ID AS CircularEntryCount
FROM dbo.CircularEntry ce
WHERE ce.AcademicYearID = 1 
  AND (ce.Circular_Date = @CurrentDate OR CAST(ce.Created_Date AS date) = @CurrentDate)
  AND (ce.CircularApplicableForID = 1 OR ce.CircularApplicableForID = 3)
  AND (','+ ce.Class_ID +',' LIKE '%,'+ CAST(@ClassID as varchar(20)) +'%,' OR ce.Class_ID = '0') 
  AND (','+ ce.Section_ID +',' LIKE '%,'+ CAST(@SectionID as varchar(20)) +'%,' OR ce.Section_ID = '0')

Note the cast to varchar(20) - bigint's min value contains a minus sign and 19 digits. If the data type of @ClassID or @SectionID is int, you can cast to varchar(11) instead.

How to check if a list of comma separated string values match an integer?

1 Answers1