如何在 SQL Server 存储过程中用逗号分隔（拆分）字符串

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

提问by Balram

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

我有一个checkboxlist. 选定（选中）的项目存储在List<string> selected.

For example, value selected is monday,tuesday,thursdayout of 7 days

例如，所选值monday,tuesday,thursday超出 7 天

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

我正在转换List<>为逗号分隔string，即

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?

我的问题是：如何在存储过程中分隔字符串？

回答by Pragnesh Khalas

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)

回答by Hadi Sharifi

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

回答by Reza

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

回答by Sapnandu

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

回答by heta naik

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:

输出：

回答by Matas Vaitkevicius

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