如何在SQL Server存储过程（分割）字符串，逗号分隔 [英] How to separate (split) string with comma in SQL Server stored procedure

问题描述

我有一个的CheckBoxList 。选定（选中）项目被存储在列表＆LT;串GT;选择。

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

例如，选择值为周一，周二，周四出7天

我转换列表＆LT;＆GT; 来用逗号分隔的字符串，即

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?

推荐答案

如果您通过分离逗号（任何分隔符）的字符串存储过程，并在查询中使用，因此必须要吐该字符串，然后你会使用它。

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.

下面有例如：

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)

这篇关于如何在SQL Server存储过程（分割）字符串，逗号分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！