Split string to columns and rows

Question

I have a string to be split; it has a format like this:

---------------------------------------------------------
HEADING DETAILS HERE:
~
INVOICE1^VALUE1^VALUE2^
INVOICE2^VALUE1^VALUE2^
INVOICE3^VALUE1^VALUE2^
TOTAL^OFVALUE1^OFVALUE2^
~
FOOTER DETAILS HERE
---------------------------------------------------------

My output should be a table with 3 columns:

|    INVOICE    |    VALUE1    |    VALUE2    |
INVOICE 1---------- VALUE1  ---------- VALUE2 
INVOICE 2---------- VALUE1  ---------- VALUE2 
INVOICE 3---------- VALUE1  ---------- VALUE2

My idea is to split with the first delimiter ~ and split 2 with delimiter ^

I made a split function and this is the result of my first split with delimiter ~

ROW 1   HEADER DETAILS HERE:

ROW 2   INVOICE1^VALUE1^VALUE2^ INVOICE2^VALUE1^VALUE2^ INVOICE3^VALUE1^VALUE2^

ROW 3   FOOTER DETAILS HERE

BUT, what I need to do is to put the results into columns instead of rows. ALSO, I need to get the result of ROW 2 and split it again and put it in columns like this:

INVOICE column -    VALUE1 column   - VALUE2 column
INVOICE 1---------- VALUE1  ---------- VALUE2 
INVOICE 2---------- VALUE1  ---------- VALUE2 
INVOICE 3---------- VALUE1  ---------- VALUE2

I hope somebody will be able to read this question and help me in the coding of my TSQL.

FOLLOW UP QUESTION: 8/13/2013
I tried to alter Rs' code to something like this...

USE [test]
GO
/****** Object:  StoredProcedure [dbo].[SplitSP4]    Script Date: 8/13/2013 7:33:31 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SplitSP4] 
    -- Add the parameters for the stored procedure here
    --<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
    @TABLE_id varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    --SET NOCOUNT ON;

declare @string varchar(8000)
declare @stringXML xml
declare @NewLine char(2)
set @NewLine=char(13)+char(10)
set @string = (SELECT [dbo].[TABLE_NAME].[iam_string] FROM [dbo].[TABLE_NAME] WHERE [dbo].[TABLE_NAME].[id] = @TABLE_id)
--'HEADING DETAILS HERE:
--~
--INVOICE1^VALUE1^VALUE2^
--INVOICE2^VALUE1^VALUE2^
--INVOICE3^VALUE1^VALUE2^
--TOTAL^OFVALUE1^OFVALUE2^
--~
--FOOTER DETAILS HERE'

declare @table1 table(cols varchar(max))

--convert string to xml
SELECT @stringXML = CONVERT(xml,'<root><i>' + 
                    Replace(@string, @NewLine, '</i><i>') + '</i></root>')

--split xml string into different rows
insert into @table1 
select Cols = I.c.value('.', 'varchar(MAX)')
from @stringXML.nodes('/root/i') I(c)

--split invoice rows into columns
select     
    Inv.value('/Invoice[1]/Attribute[1]','varchar(50)') AS [Invoice],
    Inv.value('/Invoice[1]/Attribute[2]','varchar(50)') AS [Value1],
    Inv.value('/Invoice[1]/Attribute[3]','varchar(50)') AS [Value2]
from
    (
        select CONVERT(XML,'<Invoice><Attribute>' 
            + REPLACE([cols],'~', '</Attribute><Attribute>') 
            + '</Attribute></Invoice>') Inv 
            from @table1 where cols like '%^%'
    ) x

end

...for me to be able to make a STORED PROCEDURE for a CRYSTAL REPORT. But it seems that when I executed this STORED PROCEDURE it returned this error.

Msg 9421, Level 16, State 1, Procedure SplitSP4, Line 34
XML parsing: line 1, character 31, illegal name character

I wonder how am I goint to fix my code to fit the sample XML code?

Save the values into a file and re-import them as text. That is by far the easiest way to handle such data. — Gordon Linoff
– Gordon Linoff, Commented Aug 12, 2013 at 11:00
Actually I'm doing a stored proc for a crystal report. The string field format that I'll be needing to split is like in that format. HEADING DETAILS HERE: ~ INVOICE1^VALUE1^VALUE2^ INVOICE2^VALUE1^VALUE2^ INVOICE3^VALUE1^VALUE2^ TOTAL^OFVALUE1^OFVALUE2^ ~ FOOTER DETAILS HERE — LYKS
– LYKS, Commented Aug 12, 2013 at 23:36

rs. · Accepted Answer · 2013-08-12 15:10:08Z

1

Assuming that your text has newline characters at the end of each line, try this

declare @string varchar(4000)
declare @stringXML xml
declare @NewLine char(2)
set @NewLine=char(13)+char(10)
set @string = 'HEADING DETAILS HERE:
~
INVOICE1^VALUE1^VALUE2^
INVOICE2^VALUE1^VALUE2^
INVOICE3^VALUE1^VALUE2^
TOTAL^OFVALUE1^OFVALUE2^
~
FOOTER DETAILS HERE'

declare @table1 table(cols varchar(max))

--convert string to xml
SELECT @stringXML = CONVERT(xml,'<root><i>' + 
                    Replace(@string, @NewLine, '</i><i>') + '</i></root>')

--split xml string into different rows
insert into @table1 
select Cols = I.c.value('.', 'varchar(MAX)')
from @stringXML.nodes('/root/i') I(c)

--split invoice rows into columns
select     
    Inv.value('/Invoice[1]/Attribute[1]','varchar(50)') AS [Invoice],
    Inv.value('/Invoice[1]/Attribute[2]','varchar(50)') AS [Value1],
    Inv.value('/Invoice[1]/Attribute[3]','varchar(50)') AS [Value2]
from
    (
        select CONVERT(XML,'<Invoice><Attribute>' 
            + REPLACE([cols],'^', '</Attribute><Attribute>') 
            + '</Attribute></Invoice>') Inv 
            from @table1 where cols like '%^%'
    ) x

You already have split function written, you can use last select statement above (--split invoice rows into columns) with your existing split function, convert row2 to xml and split row 2 into columns

answered Aug 12, 2013 at 15:10

rs.

27.6k13 gold badges71 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LYKS Over a year ago

Thank you so much, I'll be trying to execute your given code.

LYKS Over a year ago

Oh fantastic! Your code greatly worked. Now what I need to do is to translate this code and instead of a 'hardcoded' string i'll be getting the string value from a field in one of the tables in the database. THANK YOU SO MUCH.

LYKS Over a year ago

RS, i tried to alter your code to make a STORED PROCEDURE for a CRYSTAL REPORT out of it. But it seems to have an error... I updated my question for me to be able to display the whole code

AgentSQL · Accepted Answer · 2013-08-14 19:50:02Z

0

First, store the result from first split into a temporary table. And then you can use a cursor on that temporary table and split your rows if ^ is present in rows value.

I have created the stored procedure for you using above query -

IF OBJECT_ID(N'[dbo].[SplitSP4]', N'P') IS NOT NULL
 DROP PROCEDURE [dbo].[SplitSP4]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SplitSP4]
    @table_id INT
AS

declare @stringXML xml
declare @NewLine char(2)
DECLARE @string VARCHAR(MAX)
set @NewLine=char(13)+char(10)

declare @table1 table(cols varchar(max))

SELECT @string = string_value FROM table_name WHERE id = @table_id

--convert string to xml
SELECT @stringXML = CONVERT(xml,'<root><i>' + 
                Replace(@string, @NewLine, '</i><i>') + '</i></root>')

--split xml string into different rows
insert into @table1 
select Cols = I.c.value('.', 'varchar(MAX)')
from @stringXML.nodes('/root/i') I(c)

--split invoice rows into columns
select     
    Inv.value('/Invoice[1]/Attribute[1]','varchar(50)') AS [Invoice],
    Inv.value('/Invoice[1]/Attribute[2]','varchar(50)') AS [Value1],
    Inv.value('/Invoice[1]/Attribute[3]','varchar(50)') AS [Value2]
from
(
    select CONVERT(XML,'<Invoice><Attribute>' 
        + REPLACE([cols],'^', '</Attribute><Attribute>') 
        + '</Attribute></Invoice>') Inv 
        from @table1 where cols like '%^%'
) x

And call it like

EXEC SplitSP4 1

edited Aug 14, 2013 at 19:50

answered Aug 12, 2013 at 14:46

AgentSQL

2,94022 silver badges22 bronze badges

3 Comments

LYKS Over a year ago

I've done the first step (storing the first split into a temporary table) and now im having trouble in using cursor. Uhm.. The rows in my temp table doesn't have the carets ^ anymore. Btw, thanks for the response AgentSQL =)

LYKS Over a year ago

Im really boggled by how I should use the cursor to split these rows by 3 columns. I would really really appreciate a code that I can use as my pattern. This is my first time to actually make a stored proc for this complicated string i need for my crystal report. Thanks again AgentSQL! I'll be waiting for your response

AgentSQL Over a year ago

Sorry for late response. You can use above method mentioned by rs. Your stored procedure has a problem. See the one I posted above.

Collectives™ on Stack Overflow

Split string to columns and rows

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related