chuckh 3191 (1/27/2011)

---

You don't have to have a Tally table made in your database. Using common table expressions, you can generate them on the fly. Here is such using the example code from the article where we reaplace Tally with cteTally

--===== Declare a couple of long string variables of two different datatypes

DECLARE @LongString VARCHAR(MAX),

@NLongString NVARCHAR(MAX)

;

--===== Fill each string with 10,000 GUIDs followed by a space

-- for a total of 369999 (+1 trailing space) characters.

;WITH

cteTally AS

(--==== Create a Tally CTE from 1 to whatever the length

-- of the parameter is

SELECT TOP 20000

ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N

FROM Master.sys.All_Columns t1

CROSS JOIN Master.sys.All_Columns t2

)

SELECT @LongString = (SELECT CAST(NEWID() AS CHAR(36)) + ' '

FROM cteTally t

WHERE t.N BETWEEN 1 AND 10000

FOR XML PATH('')),

@NLongString = @LongString

;

--===== Just confirming the length of the strings here

SELECT LEN(@LongString), LEN(@NLongString)

;

--===== Let's solve the problem with a little control over the width

-- of the returned data. This could easily be converted into

-- an inline Table Valued Function.

DECLARE @Width INT;

SELECT @Width = 8000;

--===== Show that the solution works on VARCHAR(MAX)

;WITH

cteTally AS

(--==== Create a Tally CTE from 1 to whatever the length

-- of the parameter is

SELECT TOP (LEN(@LongString))

ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N

FROM Master.sys.All_Columns t1

CROSS JOIN Master.sys.All_Columns t2

)

SELECT StartPosition = (t.N-1)*@Width+1,

SliceData = SUBSTRING(@LongString,(t.N-1)*@Width+1,@Width)

FROM cteTally t

WHERE t.N BETWEEN 1 AND LEN(@LongString)/@Width+1

;