How to store semicolon separated long string into multiple columns?

Question

Basically due to restriction on live server, I cannot use text file so I need to hard code the data in T-SQL code.

So first I created string from text file like this:

("001122;Sale Item 1", "001123;Sale Item 23", "001124;Sale Item 24", .... )

I have this table structure:

DECLARE @Product TABLE(ProductCode INT NOT NULL, Description nvarchar(100) NOT NULL)

First I need to store code and description in a table variable. Once that's done, then I can easily map it to the physical table and update the records.

How can I achieve this something similar to:

insert into @Product(ProductCode, Description) 
values ("001122;Sale Item 1", "001123;Sale Item 23", "001124;Sale Item 24", .... )

 Code        Description

 001122      Sale Item1
 001123      Sale Item2
 001124      Sale Item3

Your ProductCode is INT, but you have leading zeros. They will be missing if you use datatype INT — SqlKindaGuy, Sep 14 '17 at 06:21
And if you hardcode your values, why dont u just insert them your self manually? insert into @Product(ProductCode, Description) Values ('001122','Sale Item 1'), ('001123','Sale Item 23'),( '001124','Sale Item 24') — SqlKindaGuy, Sep 14 '17 at 06:22
This does not seem you primary problem but rather a quirky workaround which you want to have fixed. Wouldn't it be better to solve your primary problem? — Martin B., Sep 14 '17 at 06:24
Possible duplicate of [Split multiple string's into multiple columns](https://stackoverflow.com/questions/27264985/split-multiple-strings-into-multiple-columns) — RealCheeseLord, Sep 14 '17 at 06:24
Sounds like a CSV to SQL conversion. See this question: https://stackoverflow.com/questions/15242757/import-csv-file-into-sql-server — Stefan Steinegger, Sep 14 '17 at 06:29

Shushil Bohara · Answer 1 · 2017-09-14T08:17:35.590

1

If you have fixed format like in example then you can achieve desired output simply using CHARINDEX and SUBSTRING

SELECT 
    SUBSTRING(description, 0, CHARINDEX(';', DESCRIPTION, 0)) code,
    SUBSTRING(description, CHARINDEX(';', DESCRIPTION, 0)+1, LEN(DESCRIPTION)) Description  
FROM @Product

OUTPUT:

 code       Description
------------------------
001122      Sale Item 1
001123      Sale Item 23
001124      Sale Item 24

edited Sep 14 '17 at 08:17

answered Sep 14 '17 at 06:35

Shushil Bohara

5,556
2
15
32

when i run your code then my output is in one row not multiple rows. – 3355307 Sep 14 '17 at 08:59

score 0 · Answer 2 · answered Sep 14 '17 at 11:48

Here we go matey:

DECLARE @MyString nvarchar(max) = '"001122;Sale Item 1", "001123;Sale Item 23", "001124;Sale Item 24"';

DECLARE @delimiter1   nvarchar(1)
      , @delimiter2   nvarchar(1)
      , @Start1       int
      , @End1         int
      , @Length       int
      , @cNEXTVALUE   nvarchar(1000)
      , @cNEXTVALUE2  nvarchar(1000)
      , @StringLength int;

DECLARE @Product TABLE ( 
                       ProductCode int NOT NULL
                     , Description nvarchar(100) NOT NULL );

SET @MyString = RTRIM(LTRIM(@MyString));
SET @MyString = REPLACE(@MyString, CHAR(13), '');
SET @MyString = REPLACE(@MyString, CHAR(10), '');

SET @delimiter1 = ';';
SET @delimiter2 = ',';
SET @MyString = REPLACE(@MyString, '"', '')+@delimiter2;
SET @StringLength = LEN(@MyString);

SELECT @Start1 = 0
     , @End1 = CHARINDEX(@delimiter1, @MyString, 1);

SELECT @Length = @End1 - @Start1;

WHILE @Length > 0
    BEGIN

        SET @cNEXTVALUE = SUBSTRING(@MyString, @Start1, @Length);
        SET @Start1 = @End1 + 1;

        SET @End1 = CHARINDEX(@delimiter2, @MyString, @Start1);
        SELECT @Length = @End1 - @Start1;

        SET @cNEXTVALUE2 = SUBSTRING(@MyString, @Start1, @Length);

        IF LEN(RTRIM(LTRIM(@cNEXTVALUE))) > 0
            BEGIN
                INSERT INTO @Product
                       ( ProductCode
                       , Description
                       ) 
                VALUES
                       ( @cNEXTVALUE, @cNEXTVALUE2 );
            END;

        SET @Start1 = @End1 + 2;
        SET @End1 = CHARINDEX(@delimiter1, @MyString, @Start1);
        SELECT @Length = @End1 - @Start1;

    END;

SELECT *
  FROM @Product;

score 0 · Accepted Answer · answered Sep 14 '17 at 12:01

I crated a sample for you, please check this

declare @Questions varchar(100)= '"001122;Sale Item 1", "001123;Sale Item 23", "001124;Sale Item 24"'

DECLARE @myXML AS XML  = N'<H><r>' +Replace(@Questions, ',', '</r><r>') + '</r></H>'
 select @myXML

;WITH cte 
AS (
    SELECT CAST(N'<H><r>' +  Replace(Vals.id.value('.', 'NVARCHAR(50)') ,';' , '</r><r>') + '</r></H>' as XML) AS val 
    FROM @myXML.nodes('/H/r') AS Vals(id) 
)

,mycte1 as ( 
SELECT   distinct
    Replace( S.a.value('(/H/r)[1]', 'NVARCHAR(50)') , '"', '') AS c1,   
    Replace( S.a.value('(/H/r)[2]', 'NVARCHAR(50)') , '"', '') AS c2

FROM cte CROSS APPLY val.nodes('/H/r') S(a)
)

select * from mycte1

The output will be

c1      c2
001123  Sale Item 23
001124  Sale Item 24
001122  Sale Item 1

How to store semicolon separated long string into multiple columns?

3 Answers3