CODEWITHSUNDEEP
asp.netsqlsql-serverstored-procedures
Balram
Balram

Reputation: 190

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

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?

Upvotes: 9

Views: 81560

Answers (7)

RBT
RBT

Reputation: 25887

Use string_split function:

CREATE PROCEDURE SPLIT_INClause
    @Technos varchar(500)
AS
BEGIN

Select * from Employees where Technology IN (SELECT value FROM STRING_SPLIT(@Technos,','))

END
GO

Upvotes: 0

heta naik
heta naik

Reputation: 1

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

Upvotes: 0

Sapnandu
Sapnandu

Reputation: 642

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,',' ))

Upvotes: 0

Matas Vaitkevicius
Matas Vaitkevicius

Reputation: 61401

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[] {','})));

Upvotes: 0

Reza
Reza

Reputation: 19843

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',','))

Upvotes: 2

Hadi Sharifi
Hadi Sharifi

Reputation: 1527

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'))

Upvotes: 3

Pragnesh Khalas
Pragnesh Khalas

Reputation: 2898

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)

Upvotes: 12

Related Questions