/*sql server 2008 CTE split and join fun*/
/*split begins*/
if object_id(N'tempdb..#split') is not null drop table #split;
if object_id(N'tempdb..#joined') is not null drop table #joined;
declare @fun varchar(64) = 'The quick brown fox jumped over the lazy dogs!';
declare @delim char(1) = ' ';
select @fun as [Fun];
with split(i, token, remainder) as
(select 1
, left(@fun,charindex(@delim,@fun)-1)
, LTRIM(right(@fun,len(@fun)-CHARINDEX(@delim,@fun)))
union all
select i + 1
,case when charindex(@delim,remainder) > 0 then
left(remainder,charindex(@delim,remainder)-1)
else remainder end as token
,LTRIM(right(remainder,len(remainder)-CHARINDEX(' ',remainder))) as remainder
from split
where charindex(@delim,remainder) >= 0 and token != remainder
)
select i, token, remainder
into #split
from split;
select * from #split;

/*join begins*/
with joined (i, newfun, token) as (
select i, convert(varchar(max),token), token
from #split where i = 1
union all
select s.i, j.newfun + @delim + s.token, s.token
from joined j
inner join
#split s
on s.i = j.i + 1
)
select *
into #joined
from joined;
select * from #joined;

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfTransformStringToColumn] (
	@String			VARCHAR(MAX),
	@Delimiter		VARCHAR(1)
)
RETURNS @List TABLE (ValueColumn VARCHAR(255) NULL)
AS
BEGIN

	IF LEFT(@String, 1) <> @Delimiter SET @String = @Delimiter + @String
	IF RIGHT(@String, 1) <> @Delimiter SET @String = @String + @Delimiter

	;WITH
	cteTally AS
	(
		SELECT	TOP (LEN(@String))
				ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
		FROM	Master.sys.All_Columns t1
				CROSS JOIN Master.sys.All_Columns t2
	)	INSERT INTO @List (ValueColumn)
			SELECT	SUBSTRING(@String,N+1,CHARINDEX(@Delimiter,@String,N+1)-N-1) AS Value
			FROM	cteTally
			WHERE	N < LEN(@String)
					AND SUBSTRING(@String,N,1) = @Delimiter	
	RETURN

END

GO

CREATE FUNCTION dbo.fn_Split 
(
	@InputString VARCHAR(MAX), 
	@Delimiter VARCHAR(MAX)
) 
RETURNS @Data TABLE (
	Data VARCHAR(MAX)
) 
AS 
BEGIN 
	DECLARE @DelimitierLen BIGINT = LEN(@Delimiter)
	DECLARE @DelimiterCharIndex BIGINT = CHARINDEX(@Delimiter,@InputString)

	WHILE (@DelimiterCharIndex > 0) 
	BEGIN 
		INSERT INTO @Data VALUES (SUBSTRING(@InputString, 1, @DelimiterCharIndex - 1))
		SET @InputString = SUBSTRING(@InputString, @DelimiterCharIndex + @DelimitierLen, LEN(@InputString)) 
		SET @DelimiterCharIndex = CHARINDEX(@Delimiter, @InputString)
	END 
	
	INSERT INTO @Data VALUES (@InputString)
	
	RETURN 
END

DECLARE @inputString varchar(MAX) = '1<i>;2;4;5;'

CREATE FUNCTION [dbo].[Split](@data VARCHAR(MAX), @delimiter VARCHAR(20))
RETURNS @t TABLE (Element VARCHAR(MAX))
AS
BEGIN
    DECLARE @textXML XML

    SET @textXML = CAST('<d>' + REPLACE(REPLACE(REPLACE(@data,'&','~~amper~~'),'<','~~lt~~'), @delimiter, '</d><d>') + '</d>' AS XML)

    INSERT INTO @t(element)
    SELECT  REPLACE(REPLACE(T.split.value('.', 'varchar(max)'),'~~amper~~','&'), '~~lt~~','<') AS data
    FROM    @textXML.nodes('/d') T(split)

    RETURN
END

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[udfTransformStringToColumn] (
	@String			VARCHAR(MAX),
	@Delimiter		VARCHAR(1)
)
RETURNS @List TABLE (ValueColumn VARCHAR(255) NULL)
AS
BEGIN

	IF LEFT(@String, 1) <> @Delimiter SET @String = @Delimiter + @String
	IF RIGHT(@String, 1) <> @Delimiter SET @String = @String + @Delimiter

	;WITH
	cteTally AS
	(
		SELECT	TOP (LEN(@String))
				ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
		FROM	Master.sys.All_Columns t1
				CROSS JOIN Master.sys.All_Columns t2
	)	INSERT INTO @List (ValueColumn)
			SELECT	SUBSTRING(@String,N+1,CHARINDEX(@Delimiter,@String,N+1)-N-1) AS Value
			FROM	cteTally
			WHERE	N < LEN(@String)
					AND SUBSTRING(@String,N,1) = @Delimiter	
	RETURN

END

GO

SELECT *
FROM [dbo].[udfTransformStringToColumn]( ';;;Token1;Token2;Token3;Token4;Token5;;',  ';' )

CREATE FUNCTION [dbo].[fnStringSplit_CTE] 
(
@SourceString VARCHAR(MAX),
@Delim CHAR(1)
)

RETURNS @Values TABLE
(
VALUE VARCHAR(MAX) 
)

AS
BEGIN
with split(i, token, remainder) as
(select 1
, left(@SourceString,charindex(@delim,@SourceString)-1)
, LTRIM(right(@SourceString,len(@SourceString)-CHARINDEX(@delim,@SourceString)))
union all
select i + 1
,case when charindex(@delim,remainder) > 0 then 
left(remainder,charindex(@delim,remainder)-1) 
else remainder end as token
,LTRIM(right(remainder,len(remainder)-CHARINDEX(@Delim,remainder))) as remainder
from split
where charindex(@delim,remainder) >= 0 and token != remainder 
)
insert into @Values
Select token
from split

RETURN
END



GO