Sajid Wasim Sajid Wasim - 6 months ago 14
SQL Question

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.

Answer

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
Comments