Insert Comma seperated varchar into multiple column value?

Question

I have varchar string value like this

"AccountType","contains","Customer","Balance","equals",250,"FirstName","like","John"

Now I want to insert this

In a table like this

  FieldName       FieldContiion  FieldValue

  AccountType     contains       Customer
  Balance         equals         250
  FirstName       like           John

The length of the string variable can be change like this

"Balance","equals",250,"FirstName","like","John"
it means that it will insert just two rows in table.

Sorry @ling.s in my case there is three fields in a table and I can't take a constant value. — Shamim, Dec 26 '13 at 13:28
Create a stored procedure to use the following to break the string up and insert into your table. http://stackoverflow.com/questions/702968/how-do-i-expand-comma-separated-values-into-separate-rows-using-sql-server-2005 — SteveB, Dec 26 '13 at 14:22

Javier · Accepted Answer · 2013-12-27T17:44:03.330

You could do the following steps:

1) Create a split function for your comma separated string:

CREATE FUNCTION [dbo].[Split] (
    @InputString                  VARCHAR(8000),
    @Delimiter                    VARCHAR(50)
)

RETURNS @Items TABLE (
    Item                          VARCHAR(8000)
)

AS
BEGIN
    IF @Delimiter = ' '
    BEGIN
          SET @Delimiter = ','
          SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
    END

    IF (@Delimiter IS NULL OR @Delimiter = '')
        SET @Delimiter = ','

    DECLARE @Item                 VARCHAR(8000)
    DECLARE @ItemList       VARCHAR(8000)
    DECLARE @DelimIndex     INT

    SET @ItemList = @InputString
    SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
    WHILE (@DelimIndex != 0)
    BEGIN
          SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
          INSERT INTO @Items VALUES (@Item)

          SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
          SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
    END

    IF @Item IS NOT NULL
    BEGIN
          SET @Item = @ItemList
          INSERT INTO @Items VALUES (@Item)
    END

    ELSE INSERT INTO @Items VALUES (@InputString)

    RETURN

END

2) Create a procedure that receives as parameter the comma separated string. In this example I'm using your fixed string but you should modify this to make it work with a parameter. Note: I'm taking in consideration that your destination table has always 3 columns and that the comma separated string has a length of a multiple of 3 always... You must adapt the procedure if this change...

/* Get the length of the comma separated string */
DECLARE @ITEM_COUNT INT

SELECT @ITEM_COUNT = COUNT(*) FROM
(
    SELECT  item
        FROM Split('"AccountType","contains","Customer","Balance","equals",250,"FirstName","like","John"',',')  
) N

declare @x int
set @x = 1

/* Insert in your table every 3 columns... */
WHILE (@x < @ITEM_COUNT)
BEGIN 

    insert into test 
    select /* pivoting the sub-query */
      fieldname = max(case when seq = @x then item end),
      fieldcondition = max(case when seq = @x + 1 then item end),
      fieldvalue = max(case when seq = @x + 2 then item end)
    from 
    (
        SELECT  item
               ,row_number() OVER (ORDER BY (SELECT 1)) AS seq 
                FROM Split('"AccountType","contains","Customer","Balance","equals",250,"FirstName","like","John"',',')  
    ) a

set @x = @x + 3

END

Hope this helps

But one thing give a problem here. The output is not come in sequence. For example when i put "AccountName","contains","ort","AccountType","contains","Customer" The result is not in sequence "AccountName" "AccountType" "contains" "contains" "Customer" "ort" — Shamim, Dec 27 '13 at 07:55
Yes, I've made a mistake when I ordered the resultset of the inner query. The solution is just to ignore the ORDER BY clause using "SELECT 1". I have updated it — Javier, Dec 27 '13 at 17:45

score 0 · Answer 2 · answered Dec 26 '13 at 17:06

Split the string over the delimiter (, in your case) and then iterate over the array of substrings and insert into the table. Your code would be something like the following :

array subStrings[] = string.split(",");
int i=0;
while(i<len(subStrings)){
    // create query using the ith, (i+1)th and (i+2)th elements of the array
i=i+3;
}

One assumption in the above code is that the main string would have elements in multiples of 3 only.

Insert Comma seperated varchar into multiple column value?

2 Answers2