Hi,
I want to know the additional features of Tally table in Sql server.
I want to split the string into multiple strings for which the below two methods worked for me in the same way.
First Method
============
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = ',Element01,Element02,Element03,Element04,Element05,'
IF OBJECT_ID('tempdb..#mytable') IS NOT NULL
DROP TABLE #mytable
SELECT TOP (select LEN(@Parameter)) identity(int,1,1)as N INTO #mytable from Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
-- SET @Parameter = ','+@Parameter +','
--===== Do the same thing as the loop did... "Step" through the variable
-- and return the character position and the character... but only
-- if it's a comma in that position...
SELECT t1.N, SUBSTRING(@Parameter,T1.N+1,CHARINDEX(',',@Parameter,T1.N+1)-T1.N-1)val
FROM #mytable T1 inner join #mytable t2 on t1.n=t2.n-1
WHERE t1.N <= LEN(@Parameter)
AND SUBSTRING(@Parameter,t1.N,1) = ','
ORDER BY N
Tally Table
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = ',Element01,Element02,Element03,Element04,Element05,'
IF OBJECT_ID('DBO.TALLY') IS NOT NULL
DROP TABLE DBO.TALLY
SELECT TOP (select LEN(@Parameter)) identity(int,1,1)as N INTO dbo.tally from Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
-- SET @Parameter = ','+@Parameter +','
--===== Do the same thing as the loop did... "Step" through the variable
-- and return the character position and the character... but only
-- if it's a comma in that position...
SELECT t1.N, SUBSTRING(@Parameter,T1.N+1,CHARINDEX(',',@Parameter,T1.N+1)-T1.N-1)
FROM dbo.Tally T1 inner join dbo.tally t2 on t1.n=t2.n-1
WHERE t1.N <= LEN(@Parameter)
AND SUBSTRING(@Parameter,t1.N,1) = ','
ORDER BY N
Please advise
Thanks
What I have tried:
Both tally table and temp table works same for me