单独的逗号分隔值并存储在sql server中的表中

I am having a stored procedure which gets the comma separated value as an input. I need to separate it and needs to store it in a table as individual rows.

我有一个存储过程,它将逗号分隔值作为输入。我需要将它分开,并需要将它作为单独的行存储在表中。

Let the input for SP is :

让SP的输入是:

Rule_ID  ListType_ID  Values
1        2            319,400,521,8465,2013

I need to store it in a table called DistributionRule_x_ListType in the below format:

我需要将它存储在一个名为DistributionRule_x_ListType的表中,格式如下:

Rule_ID  ListType_ID  Value
1        2            319
1        2            400
1        2            521
1        2            8465
1        2            2013

My SP looks like below:

我的SP如下所示:

ALTER PROCEDURE [dbo].[spInsertDistributionRuleListType]
(@Rule_ID int,
@ListType_ID int,
@Values VARCHAR(MAX)=NULL
)
AS
BEGIN

    INSERT INTO DistributionRule_x_ListType (Rule_ID,ListType_ID,Value)
    VALUES (@Rule_ID,@ListType_ID,@Values)

END

6 个解决方案

#1

You will need to create a split function similar to this:

您需要创建一个类似于此的拆分函数:

create FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;

Then in your stored procedure, you will call the function to split your string:

然后在您的存储过程中,您将调用该函数来拆分您的字符串:

ALTER PROCEDURE [dbo].[spInsertDistributionRuleListType]
(
  @Rule_ID int,
  @ListType_ID int,
  @Values VARCHAR(MAX)=NULL
)
AS
BEGIN

    INSERT INTO DistributionRule_x_ListType (Rule_ID, ListType_ID, Value)
    SELECT @Rule_ID, @ListType_ID, items
    FROM [dbo].[Split] (@Values, ',')  -- call the split function 

END

When you execute the stored procedure, it will split the values and insert the multiple rows into your table:

执行存储过程时,它将拆分值并将多行插入表中:

exec spInsertDistributionRuleListType 1, 2, '319,400,521,8465,2013';

See SQL Fiddle with Demo. This will insert the following result:

请参阅SQL Fiddle with Demo。这将插入以下结果:

| RULE_ID | LISTTYPE_ID | VALUE |
---------------------------------
|       1 |           1 |    10 |
|       1 |           2 |   319 |
|       1 |           2 |   400 |
|       1 |           2 |   521 |
|       1 |           2 |  8465 |
|       1 |           2 |  2013 |

#2

you can do it with charindex like

你可以用charindex这样做

DECLARE @id VARCHAR(MAX)

SET @id = @Values          --'319,400,521,8465,2013,'

WHILE CHARINDEX(',', @id) > 0 
BEGIN

    DECLARE @tmpstr VARCHAR(50)
     SET @tmpstr = SUBSTRING(@id, 1, ( CHARINDEX(',', @id) - 1 ))

    INSERT  INTO DistributionRule_x_ListType
            ( Rule_ID ,
              ListType_ID ,
              Value
            )
    VALUES  ( @Rule_ID ,
              @ListType_ID ,
              @tmpstr)
            )
    SET @id = SUBSTRING(@id, CHARINDEX(',', @id) + 1, LEN(@id))
END

#3

You can do this without dbo.Split function.

您可以在没有dbo.Split功能的情况下执行此操作。

Here is your sample data

这是您的示例数据

SELECT * INTO #TEMP
FROM
(
     SELECT 1  Rule_ID, 2 ListType_ID, '319,400,521,8465,2013' [Values]
     UNION ALL
     SELECT 1  , 3 , '100,200' 
)TAB

Now execute the following query and will select all comma separated values for each Rule_ID and ListType_ID.

现在执行以下查询,并为每个Rule_ID和ListType_ID选择所有逗号分隔值。

SELECT [Rule_ID],ListType_ID,
PARSENAME(REPLACE(Split.a.value('.', 'VARCHAR(100)'),'-','.'),1) 'Values' 
FROM  
(
     SELECT [Rule_ID],ListType_ID,
     CAST ('<M>' + REPLACE([Values], ',', '</M><M>') + '</M>' AS XML) AS Data 
     FROM #TEMP     
) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a)

• SQL FIDDLE

#4

select Rule_ID ,ListType_ID,Values as value
FROM table  
    CROSS APPLY STRING_SPLIT(Values, ',');  

#5

You could create a udf that returns a table var to split the string. We have used the following successfully on MSSQL2005.

您可以创建一个返回表var的udf来拆分字符串。我们在MSSQL2005上成功使用了以下内容。

CREATE FUNCTION [dbo].[fn_explode] (
    @str_separator NVARCHAR(255),
    @str_string VARCHAR(4000)
)
RETURNS @ret_string_parts TABLE (str_value varchar(4000))
AS
BEGIN
    DECLARE @intPos INT
    DECLARE @intLengthString INT
    DECLARE @intTempPatIndex INT
    DECLARE @intLengthSeparator INT

    SET @str_string = @str_string + @str_separator

    SET @intPos = 0
    SET @intLengthString = LEN(@str_string)
    SET @intLengthSeparator = LEN(@str_separator)



    IF PATINDEX ( '%' + @str_separator + '%' , @str_string ) <= 0 BEGIN
        INSERT INTO @ret_string_parts
        SELECT @str_string
        RETURN
    END

    IF @str_separator =  @str_string BEGIN
        INSERT INTO @ret_string_parts
        SELECT @str_string
        RETURN
    END

    WHILE @intPos <= @intLengthString
    BEGIN
        SET @intTempPatIndex = PATINDEX('%' + @str_separator + '%', SUBSTRING(@str_string, @intPos,@intLengthString))
        IF @intTempPatIndex = 0 BEGIN
            INSERT INTO @ret_string_parts
            SELECT SUBSTRING(@str_string, @intPos, @intLengthString)
            BREAK
        END
        ELSE BEGIN
            IF @intPos = 0 BEGIN
                INSERT INTO @ret_string_parts
                SELECT SUBSTRING(@str_string, @intPos, @intTempPatIndex)
                SET @intPos = @intPos + @intTempPatIndex + @intLengthSeparator
            END
            ELSE BEGIN
                INSERT INTO @ret_string_parts
                SELECT SUBSTRING(@str_string, @intPos, @intTempPatIndex-1)
                SET @intPos = @intPos + @intTempPatIndex + (@intLengthSeparator-1)
            END

        END
    END

    RETURN
END

#6

Completing @bluefeet answer, you could also use the CSV string to store multiple values in multiple columns:

完成@bluefeet答案后,您还可以使用CSV字符串在多个列中存储多个值:

 --input sql text 
declare @text_IN varchar(max) ='text1, text1.2, text1.3, 1, 2010-01-01\r\n text2, text2.2, text2.3, 2, 2016-01-01'

Split the csv file into rows:

将csv文件拆分为行:

declare @temptable table (csvRow varchar(max))    
declare @DelimiterInit varchar(4) = '\r\n'
declare @Delimiter varchar(1) = '|'
declare @idx int       
declare @slice varchar(max)    

set @text_IN = REPLACE(@text_IN,@DelimiterInit,@Delimiter)


select @idx = 1       
    if len(@text_IN)<1 or @text_IN is null  return       

while @idx!= 0       
begin       
    set @idx = charindex(@Delimiter,@text_IN)       
    if @idx!=0       
        set @slice = left(@text_IN,@idx - 1)       
    else       
        set @slice = @text_IN 

    if(len(@slice)>0)  
        insert into @temptable(csvRow) values(@slice)       

    set @text_IN = right(@text_IN,len(@text_IN) - @idx)       
    if len(@text_IN) = 0 break       
end   

Split rows into columns:

将行拆分为列:

;WITH XMLTable (xmlTag)
AS
(
    SELECT CONVERT(XML,'<CSV><champ>' + REPLACE(csvRow,',', '</champ><champ>') + '</champ></CSV>') AS xmlTag
    FROM @temptable
)

 SELECT RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[1]','varchar(max)'))) AS Column1,    
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[2]','varchar(max)'))) AS Column2,
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[3]','varchar(max)'))) AS Column3,    
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[4]','int'))) AS Column4,
        RTRIM(LTRIM(xmlTag.value('/CSV[1]/champ[5]','datetime'))) AS Column5
 FROM XMLTable