It can be do it that way. instead of using two delimiter in one query you can use it into two places.
CREATE FUNCTION [dbo].[SPLIT]
( @DELIMITER VARCHAR(5),
@LIST VARCHAR(MAX)
)
RETURNS @TABLEOFVALUES TABLE
( ROWID SMALLINT IDENTITY(1,1),
[VALUE] VARCHAR(MAX)
)
AS
BEGIN
DECLARE @LENSTRING INT
WHILE LEN( @LIST ) > 0
BEGIN
SELECT @LENSTRING =
(CASE CHARINDEX( @DELIMITER, @LIST )
WHEN 0 THEN LEN( @LIST )
ELSE ( CHARINDEX( @DELIMITER, @LIST ) -1 )
END
)
INSERT INTO @TABLEOFVALUES
SELECT SUBSTRING( @LIST, 1, @LENSTRING )
SELECT @LIST =
(CASE ( LEN( @LIST ) - @LENSTRING )
WHEN 0 THEN ''
ELSE RIGHT( @LIST, LEN( @LIST ) - @LENSTRING - 1 )
END
)
END
RETURN
END
GO
And then create a temp table or table variable to store the output to further split.
Declare @Outputval as nvarchar(max)
CREATE table #temptable
(
RowID int,
OutputVal nvarchar(max)
)
Insert into #temptable
select * from dbo.SPLIT(';','T001,20 Sep 2015,9/20/2015 22:00:00,9/20/2015 23:00:00;T002,20 Sep 2015,9/20/2015 22:00:00,9/21/2015 06:00:00;T003,20 Sep 2015,9/21/2015 01:00:00,9/21/2015 06:00:00')
select @Outputval = OutputVal from #temptable
select * from dbo.SPLIT(',', @Outputval)
Query can be more simplify, i just try to give you idea.
Thanks
Submit an article or tip