Splitting multiple delimited values into multiple rows

Question

I have been looking for a solution in StackOverflow but didn't find anything useful. I am facing a issue and I hope anyone would like to help me out.

I have value like this:

Create table DemoRecords
(
    CustID int identity (1,1),
    CustomerName varchar(50),
    CurrencyCode varchar(50),
    CurrentBalance varchar(50),
    DateValue varchar(50)
)
GO

INSERT INTO DemoRecords VALUES ('Mr. X', 'BDTýUSDýGBP','10500ý2500ý1050','20150101ý20150201ý20150301')

..and I need output like this: (Please take a look at the picture attached below)

Picture

Please don't suggest me to use CTE because there are more than 100 columns in that table.

You can write a simple loop to pull out the bits you care about and put them in your database. — Toby Allen, May 15 '16 at 08:41
Note: Even after you split the values you will still need to translace the DateValue to an actual date datatype. — Zohar Peled, May 15 '16 at 09:04
Could you please explain a bit more cause I am totally confused :( — Sajid Wasim, May 15 '16 at 09:07

matt.dolfin · Accepted Answer · 2016-05-15T20:17:03.257

Here is a function to split a string into rows. Below that is a query against your demorecords table that uses the function to get the requested result.

create function dbo.split
(
    @delimited nvarchar(max),
    @delimiter nvarchar(5)
)
returns @rows table
(
    rownumber int not null identity(1,1),
    value nvarchar(max) not null
)
as
begin
    if @delimited is null return

    declare @delr_len int = len(@delimiter)
    declare @start_at int = 1
    declare @end_at int
    declare @deld_len int

    while 1=1
    begin
        set @end_at = charindex(@delimiter,@delimited,@start_at)
        set @deld_len = case @end_at when 0 then len(@delimited) else @end_at-@start_at end
        insert into @rows (value) values( substring(@delimited,@start_at,@deld_len) );
        if @end_at = 0 break;
        set @start_at = @end_at + @delr_len
    end

    return
end
go


select custid, customername, currencycode=currencycode.value, currentbalance=currentbalance.value, datevalue=datevalue.value
    from demorecords r
    cross apply (select rownumber, value from dbo.split(r.currencycode,'ý') ) currencycode
    cross apply (select rownumber, value from dbo.split(r.currentbalance,'ý') where rownumber = currencycode.rownumber ) currentbalance
    cross apply (select rownumber, value from dbo.split(r.datevalue,'ý') where rownumber = currencycode.rownumber ) datevalue

If you have a column that may contain missing values, use an outer apply instead of an inner apply to join the result of the function for that column. In the following example, the DateValue column is missing value 3 and value 4.

INSERT INTO DemoRecords VALUES ('Mr. X', 'BDTýUSDýGBPýEUR','10500ý2500ý1050ý','ý')

select custid, customername, currencycode=currencycode.value, currentbalance=currentbalance.value, datevalue=datevalue.value
from demorecords r
cross apply (select rownumber, value from dbo.split(r.currencycode,'ý') ) currencycode
cross apply (select rownumber, value from dbo.split(r.currentbalance,'ý') where rownumber = currencycode.rownumber ) currentbalance
outer apply (select rownumber, value from dbo.split(r.datevalue,'ý') where rownumber = currencycode.rownumber ) datevalue

Alternatively, you could clean up your input to not be missing values. In the above example, I would expect DateValue to be 'ýýý' not 'ý'. If your situation allows it, you might prefer finding and fixing these and not using an outer join.

Dear Matt, I have a scenario. Could you please run your function on this below data and see what happens. INSERT INTO DemoRecords VALUES ('Mr. X', 'BDTýUSDýGBPýEUR','10500ý2500ý1050ý','ý'). the rownumber is not matching with other columns so not getting the expected output. — Sajid Wasim, May 15 '16 at 12:36
@Sajid Wasim, in your latest scenario, DateValue is missing the third and fourth values. I would expect it to have a value of 'ýýý', not 'ý'. You could just use an outer apply instead of an inner apply to join the result of the function for that column. See my updated answer. Or, you could clean your input to have the correct number of columns. — matt.dolfin, May 15 '16 at 20:26
Dear Matt, let me thank you again. I used your solution on my 200K data file and it's taking more 8 hours to split :(. Is it possible for you to suggest me anything else? — Sajid Wasim, May 17 '16 at 05:28

Splitting multiple delimited values into multiple rows

1 Answers1