Search code examples
sqlsql-serversplitdelimited

Splitting multiple delimited values into multiple rows

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)

enter image description here Picture

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

Solution

  • 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.