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?

You don't have to separate strings in proc, instead you can use `IN`. If you are looking to split values then create a function based on [this answer](http://stackoverflow.com/a/10581814/961113) and then use it — Habib, Apr 15 '14 at 16:41
What if instead of OR we had to use AND i.e WHERE days = 'Monday' AND days = 'Tuesday' — Radhesh Khanna, Mar 18 '21 at 07:08

score 12 · Accepted Answer · answered Apr 15 '14 at 16:54

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)

Use dbo.ufnSplit function that I answered is better and clear code on store procedure. — Hadi Sharifi, Apr 15 '14 at 16:56

score 3 · Answer 2 · answered Apr 15 '14 at 16:54

Try this:

CREATE FUNCTION [dbo].[ufnSplit] (@string NVARCHAR(MAX))
RETURNS @parsedString TABLE (id NVARCHAR(MAX))
AS 
BEGIN
   DECLARE @separator NCHAR(1)
   SET @separator=','
   DECLARE @position int
   SET @position = 1
   SET @string = @string + @separator
   WHILE charindex(@separator,@string,@position) <> 0
      BEGIN
         INSERT into @parsedString
         SELECT substring(@string, @position, charindex(@separator,@string,@position) - @position)
         SET @position = charindex(@separator,@string,@position) + 1
      END
     RETURN
END

Then use this function,

Select * 
from tblx 
where days IN (SELECT id FROM [dbo].[ufnSplit]('monday,tuesday,thursday'))

score 2 · Answer 3 · answered Apr 15 '14 at 16:59

try this

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(max)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END
GO

usage:

select * from tblx where days in (select val from dbo.split('monday,tuesday,thursday',','))

Matas Vaitkevicius · Answer 4 · 2015-02-05T09:49:00.870

0

I think you want this

SELECT * FROM tblx where days in ('Monday','Tuesday','Thursday')

you can get it like this:

var a = "monday,tuesday,thursday";
var sql = string.Format("Select * from tblx where days IN ('{0}')", string.Join("','",a.Split(new[] {','})));

edited Feb 05 '15 at 09:49

answered Apr 15 '14 at 16:39

Matas Vaitkevicius

58,075
31
238
265

1

thanks for response,,but sir,,what if user selects tuesday and wednesday from that checkboxlist – Balram Apr 15 '14 at 17:39

score 0 · Answer 5 · answered Mar 03 '17 at 12:24

I face the same problem, and i try all the way but not get expected solution. Finally i did like follow. Try it hope it will work...

create Function [dbo].[Split]
(
   @RowData NVARCHAR(MAX),
   @SplitOn NVARCHAR(5)
)
RETURNS @RtnValue TABLE
(
   Id INT IDENTITY(1,1),
   Data NVARCHAR(100)
)
AS
BEGIN 
   DECLARE @Cnt INT
   SET @Cnt = 1
   WHILE (Charindex(@SplitOn,@RowData)>0)
   BEGIN
          INSERT INTO @RtnValue (data)
          SELECT Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
          SET @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
          SET @Cnt = @Cnt + 1
   END
   INSERT INTO @RtnValue (data)
   SELECT Data = ltrim(rtrim(@RowData))
   RETURN
END

And in the store procedure put the code like that.

select @ActualTarget= count(*) from UpdateVisitDetails where CreatedBy IN (SELECT [DATA] FROM [dbo].[Split](@AllDATS,',' ))

score 0 · Answer 6 · edited Nov 02 '18 at 22:28

I have same problem. I tried this.. and this was properly run

ALTER FUNCTION [dbo].[Split]
(
  @List varchar(max),
  @SplitOn nvarchar(5)
)

RETURNS @RtnValue table
(
  Id int identity(1,1),
  Value nvarchar(max)
)
AS
BEGIN
IF (len(@List) <=0)
Begin
Return 
End

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
END

Run : 
SELECT * FROM dbo.Split('Apple,Banana,Mango',',')

Output:

enter image description here

How to separate (split) string with comma in SQL Server stored procedure

6 Answers6

Linked