asp.net - How to separate (split) string with comma in SQL Server stored procedure

Question

I have a checkboxlist. The selected (checked) items are stored in List<string> selected.

For example, value selected is monday,tuesday,thursday out of 7 days

I am converting List<> to a comma-separated string, i.e.

string a= "monday,tuesday,thursday"

Now, I am passing this value to a stored procedure as a string. I want to fire query like:

Select * 
from tblx 
where days = 'Monday' or days = 'Tuesday' or days = 'Thursday'`

My question is: how to separate string in the stored procedure?

Answer 1

If you pass the comma separated (any separator) string to store procedure and use in query so must need to spit that string and then you will use it.

Below have example:

DECLARE @str VARCHAR(500) = 'monday,tuesday,thursday'
CREATE TABLE #Temp (tDay VARCHAR(100))
WHILE LEN(@str) > 0
BEGIN
    DECLARE @TDay VARCHAR(100)
    IF CHARINDEX(',',@str) > 0
        SET  @TDay = SUBSTRING(@str,0,CHARINDEX(',',@str))
    ELSE
        BEGIN
        SET  @TDay = @str
        SET @str = ''
        END
  INSERT INTO  #Temp VALUES (@TDay)
 SET @str = REPLACE(@str,@TDay + ',' , '')
 END

 SELECT * 
 FROM tblx 
 WHERE days IN (SELECT tDay FROM #Temp)