Split string into multiple rows with multiple columns in paired

Question

I have a table as below:

Order No | Customers            | Amount    
---------+----------------------+-------------
1        | Briant~~Luck         | 23~~2122 
2        | Mike~~Lee~~David     | 10~~200~37 
3        | Stak                 | 100

With format, each customer has one value in Amount.

I'm trying to figure out how to expand the ~~ delimited values to populate a new customers table, which should look like this:

Order No | Customer             | Amount    
---------+----------------------+---------
1        | Briant               |   23
1        | Luck                 | 2122 
2        | Mike                 |   10 
2        | Lee                  |  200
2        | David                |   37 
3        | Stak                 |  100

How can I do?

Any solution in SQL query, function or cursor is appreciated.

Thanks

Check this case https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x#comment63464157_2647 — RoMEoMusTDiE, Jun 01 '17 at 08:43
Basically what you are trying to do is deconstruct a stuffed result — RoMEoMusTDiE, Jun 01 '17 at 08:44

score 2 · Answer 1 · answered Jun 01 '17 at 09:12

I think you could store data as your expected result structure. It is much better.
Btw you could use a split function to get your output

DECLARE @SampleData AS TABLE
(
    OrderNo int,
    Customers varchar(200),
    Amount varchar(200)
)

INSERT INTO @SampleData
(
    OrderNo,
    Customers,
    Amount
)
VALUES
( 1, 'Briant~~Luck','23~~2122'), 
( 2, 'Mike~~Lee~~David','10~~200~~37'),
( 3, 'Stak','100')


SELECT sd.OrderNo, c.[Value] AS Customer, a.[Value] AS Amount
FROM @SampleData sd
CROSS APPLY 
(
    SELECT Pos, Value
    FROM [dbo].[SplitString](sd.Customers,'~~')
) c
CROSS APPLY 
(
    SELECT Pos, Value
    FROM [dbo].[SplitString](sd.Amount,'~~')
) a
WHERE c.Pos = a.Pos
ORDER BY sd.OrderNo

Split function

CREATE FUNCTION [dbo].[SplitString] (@Text varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
   Select Pos = Row_Number() over (Order By (Select null))
        ,Value = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
   From (Select x = Cast('<x>'+ Replace(@Text,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
   Cross Apply x.nodes('x') AS B(i)
);

Demo link: http://rextester.com/XRX32958

LukStorms · Answer 2 · 2017-06-01T10:14:01.493

This solution uses XML, CROSS APPLY & ROW_NUMBER to deconstruct the '~~' seperated fields.

It doesn't require a UDF or the STRING_SPLIT function from SQL Server 2016.

-- Using a table variable for the test
declare @Orders table ([Order No] int, Customers varchar(30), Amount varchar(30));
insert into @Orders ([Order No], Customers, Amount) values
(1,'Briant~~Luck','23~~2122'),
(2,'Mike~~Lee~~David','10~~200~~37'),
(3,'Stak','100');

SELECT C.[Order No], C.Customer, A.Amount
FROM
(   
    SELECT 
     [Order No], 
     row_number() over (partition by [Order No] order by (select 1)) as rn,
     Customers.Name.value('.', 'VARCHAR(100)') AS Customer
    FROM (
        SELECT [Order No], CAST ('<x>' + REPLACE(Customers, '~~', '</x><x>') + '</x>' AS XML) AS XCustomers
        FROM @Orders
    ) AS Q1 
    CROSS APPLY Q1.XCustomers.nodes ('/x') AS Customers(Name)
) C
JOIN (
    SELECT 
     [Order No], 
     row_number() over (partition by [Order No] order by (select 1)) as rn,
     Amounts.Value.value('.', 'VARCHAR(100)') AS Amount
    FROM (
        SELECT [Order No], CAST ('<x>' + REPLACE(Amount, '~~', '</x><x>') + '</x>' AS XML) AS XAmounts
        FROM @Orders
    ) AS Q1 
    CROSS APPLY Q1.XAmounts.nodes ('/x') AS Amounts(Value)
) A 
ON (C.[Order No] = A.[Order No] AND C.RN = A.RN);

Or if you know there will be maximum 3 values in those strings.
Then the trick with PARSENAME could be used:

SELECT [Order No], 
PARSENAME(REPLACE(Customers, '~~', '.'), v.n) as Customer, 
PARSENAME(REPLACE(Amount, '~~', '.'), v.n) as Amount
FROM @Orders t
JOIN (VALUES (1),(2),(3)) AS v(n)
ON v.n <= (len(Customers) - len(replace(Customers, '~~', ','))+1);

score 0 · Answer 3 · answered Jun 01 '17 at 09:09

If you are on SQL2016+ then try this:

drop table if exists dbo.Orders;

create table dbo.Orders (
ID int
, Customers varchar(100)
, Amount varchar(100)
);

insert into dbo.Orders (ID, Customers, Amount)
values (1,'Briant~~Luck', '23~~2122')
, (2, 'Mike~~Lee~~David', '10~~200~~37')
, (3, 'Stak', '100');


select
o.ID
, t01.value as Customer
, t02.value as Amount
from dbo.Orders o
outer apply (
    select
        ROW_NUMBER () over (order by o.Customers ASC) as Rbr
        , t.value
    from string_split (replace(o.Customers, '~~', '~'), '~') t
) t01
outer apply (
    select
        ROW_NUMBER () over (order by o.Amount ASC) as Rbr
        , t.value
    from string_split (replace(o.Amount, '~~', '~'), '~') t
) t02
where t01.Rbr = t02.Rbr

Note that this requires SQL Server 2016 – iamdave Jun 01 '17 at 09:10 — iamdave, Jun 01 '17 at 09:10

score 0 · Answer 4 · answered Jun 01 '17 at 09:13

If you are on a version of SQL Server prior to 2016, you will need to create your own String Splitting function and reference that in your script. The version I use is as follows:

create function [dbo].[StringSplit]
(
    @str nvarchar(4000) = ' '               -- String to split.
    ,@delimiter as nvarchar(1) = ','        -- Delimiting value to split on.
    ,@num as int = null                     -- Which value to return.
)
returns table
as
return
(                       -- Start tally table with 10 rows.
    with n(n)   as (select n from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n(n))
                        -- Select the same number of rows as characters in @str as incremental row numbers.
                    -- Cross joins increase exponentially to a max possible 10,000 rows to cover largest @str length.
        ,t(t)   as (select top (select len(@str) a) row_number() over (order by (select null)) from n n1,n n2,n n3,n n4)
                            -- Return the position of every value that follows the specified delimiter.
        ,s(s)   as (select 1 union all select t+1 from t where substring(@str,t,1) = @delimiter)
                            -- Return the start and length of every value, to use in the SUBSTRING function.
                    -- ISNULL/NULLIF combo handles the last value where there is no delimiter at the end of the string.
        ,l(s,l) as (select s,isnull(nullif(charindex(@delimiter,@str,s),0)-s,4000) from s)
    select rn as ItemNumber
            ,Item
    from(select row_number() over(order by s) as rn
                ,substring(@str,s,l) as item
        from l
        ) a
    where rn = @num         -- Return a specific value where specified,
        or @num is null     -- Or the everything where not.
)

And is used as follows. Note that I have split the outer apply into separate queries to avoid row duplication:

declare @t table(OrderNo int,Customers nvarchar(500),Amount nvarchar(500));
insert into @t values
 (1,'Briant~~Luck','23~~2122')
,(2,'Mike~~Lee~~David','10~~200~~37')
,(3,'Stak','100');

with c as
(
    select t.OrderNo
            ,c.ItemNumber
            ,c.Item as Customers
    from @t t
        outer apply dbo.StringSplit(replace(t.Customers,'~~','|'),'|',null) c
),a as
(
    select t.OrderNo
            ,a.ItemNumber
            ,a.Item as Amount
    from @t t
        outer apply dbo.StringSplit(replace(t.Amount,'~~','|'),'|',null) a
)
select c.OrderNo
        ,c.Customers
        ,a.Amount
from c
    join a
        on(c.OrderNo = a.OrderNo
            and c.ItemNumber = a.ItemNumber
            )
order by a.OrderNo
        ,c.Customers;

Output:

+---------+-----------+--------+
| OrderNo | Customers | Amount |
+---------+-----------+--------+
|       1 | Briant    |     23 |
|       1 | Luck      |   2122 |
|       2 | David     |     37 |
|       2 | Lee       |    200 |
|       2 | Mike      |     10 |
|       3 | Stak      |    100 |
+---------+-----------+--------+

score 0 · Answer 5 · answered Jun 01 '17 at 09:18

Here solution as plsql

declare
type t_array_text is table of varchar2(30);
type t_array_number is table of number;

array_text t_array_text := t_array_text();
array_number t_array_number := t_array_number();
i number := 1;

cursor c1 is
    select * from deneme;

begin

    FOR c in c1

    LOOP

         i := 1;

         array_text := t_array_text();
        array_number := t_array_number();


        for rec in (
            SELECT regexp_substr(c.customer, '[[:alpha:]]+', 1, level) a frOM dual
            CONNECT BY level<=regexp_count(c.customer,'~~')+1)
        loop
            array_text.extend();
            array_text (i) := rec.a;
            i := i + 1;
        end loop;

        i := 1;

        for rec in (
            SELECT regexp_substr(c.amount, '[0-9]+', 1, level) a frOM dual
            CONNECT BY level<=regexp_count(c.amount,'~~')+1)
        loop
            array_number.extend();
            array_number (i) := rec.a;
            i := i + 1;
        end loop;

        for y in 1..array_text.count loop
            dbms_output.put_line (c.order_no || ' ' || array_text(y) || ' ' || array_number(y));
        end loop;

    END LOOP;

end;

result as follows:

1 Briant 23
1 Luck 2122
2 Mike 10
2 Lee 200
2 David 37
3 Stak 10

Split string into multiple rows with multiple columns in paired

5 Answers5

Linked