SQL help needed with a split function sql server 2008

Yes of course here is the script

Step 1 - Create the Tally Table

--=============================================================================

-- Setup. Duration - 00:02:31 Rows - 158,533,281

--=============================================================================

USE Source_Cleansed_DM

SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance/speed

DECLARE @StartTime DATETIME --Timer to measure total duration

SET @StartTime = GETDATE() --Start the timer

--=============================================================================

-- Create and populate a Tally table

--=============================================================================

--===== Conditionally drop

IF OBJECT_ID('dbo.Tally') IS NOT NULL

DROP TABLE dbo.Tally

--===== Create and populate the Tally table on the fly

SELECT TOP 158533281 --158,533,281

IDENTITY(INT,1,1) AS N

INTO dbo.Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Let the public use it

GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC

--===== Display the total duration - 221830 Milliseconds duration

SELECT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds duration'

select COUNT(*) from dbo.Tally

Step 2 and 3 - Create temp table and run split to normalize table. (i've commented out the create sample table as this only needs to be run once obviously)

--===== Create a sample denormalized table with a CSV column

-- CREATE TABLE #testtable

-- (PK INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

-- ,Fact1 nvarchar(20)

--,Fact2 nvarchar(20)

-- ,[Values] nvarchar(max))

--insert into #testtable (Fact1, Fact2, [Values])

--VALUES ('test1','Fact2','1,2.9,4,9')

--insert into #testtable (Fact1, Fact2, [Values])

--VALUES ('test3','Fact4','2,6,7.3,5.5')

--insert into #testtable (Fact1, Fact2, [Values])

--VALUES ('test5','Fact4','4,7,9')

--insert into #testtable (Fact1, Fact2, [Values])

--VALUES ('test1','Fact9','114,514,5,343.45,8,234,5,912,345')

--insert into #testtable (Fact1, Fact2, [Values])

--VALUES ('test1','Fact2','11,4514,5,343.45,82,345,9,1,2345')

--===== Split or "Normalize" the whole table at once

SELECT tt.PK,tt.Fact1,tt.Fact2,

SUBSTRING(','+tt.[Values]+',',N+1,CHARINDEX(',',','+tt.[Values]+',',N+1)-N-1) AS Value

FROM dbo.Tally t

CROSS JOIN #testtable tt

WHERE t.N < LEN(','+tt.[Values]+',')

AND SUBSTRING(','+tt.[Values]+',',N,1) = ','

Sichy (1/9/2011)

I'm really curious about this line of code:

SELECT TOP 158533281 --158,533,281

What does that number represent? It's a really odd number to use - why not 200 million?

WayneS (1/9/2011)

I'll add to that... why so big? Are you really splitting strings with that many bytes in them?

Good question! At first i actually didn't have access to the table and i couldn't imagine how many rows it would initialy have so i simply put 999999999999... When i ran the tally table and did a count of rows i found that it actually maxed out at 158,533,281. So when i needed to create the tally table on a different DB i just replaced my 99999999999 with 158,533,281 to save a few mins on the tally table creation.

p.s. Turns out the initial table only has about 900,000 rows so now I've changed the number to 1,000,000 to save even more time.

Oh forgot to mention, Thanks Jeff Moden for writing the article 🙂

There's a bit of a misnomer that you may have taken. The Tally Table [font="Arial Black"]doesn't [/font]need the same number of rows as the table data that you're trying to split. It only needs the same number of rows as the largest number of bytes in a column that you're trying to split. Generally speaking, the largest datatype most folks need to split is VARCHAR(8000). With that in mind, the largest your Tally Table needs to be to be able to do splits on such a column is only 8000 numbers.

And, thanks for the feedback on the article.

Ahhh yes i see. Thanks again Jeff your help is greatly appreciated!

JS

You bet. Thanks for the feedback.