Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Best split function

Author  Topic 

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-03 : 09:10:33
[url]http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/treatyourselfListing_01.txt[/url]

This can't be the best SQL string split function around, and it's the 3rd string split function I've borrowed to solve a problem. To stop spaghetti creep, I've got to choose one and go with it.

I could pick one of the 3 string split functions I'm using, but maybe if I poll the community at large with a large pole I'll find some string split function out there that's better than sliced bread.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-03 : 09:28:01
yeah... here's mine

CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End

Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))

Return
END


Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-06-05 : 03:41:12
And mine.
Its gone through a lot of iterations for various edge-conditions we had.
Probably still got bugs though.
Needs a Tally Table (CREATE provided).
Sorry its probably a bit wide.

Kristen

--
PRINT 'Create function KK_FN_DelimSplit_V2'
GO
EXEC dbo.kk_sm_SP_LogScriptRun 'KK_FN_DelimSplit_V2', '040717'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[KK_FN_DelimSplit_V2]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION [dbo].[KK_FN_DelimSplit_V2]
GO

CREATE FUNCTION dbo.KK_FN_DelimSplit_V2
(
@strSource varchar(8000),
@strDelimiter varchar(10) = ',',
@strBlank varchar(8000) = '[DELETE]' -- What to return if an item is blank? (e.g. '', NULL or '[BLANK]')
-- Use '[DELETE]' to delete any NULL items
)
RETURNS @tblArray TABLE
(
Item int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Value varchar(8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
/* WITH ENCRYPTION */
AS
/*
* KK_FN_DelimSplit_V2 Split a string based on delimiter
*
* SELECT * FROM dbo.KK_FN_DelimSplit_V2('A,B,C', ',', NULL)
*
* Returns:
*
* Resultset of Value/Value pairs
*
* HISTORY:
*
* 04-Sep-2003 KBM V2 - Changed Item/Value to Value/Value. Fixed bug final value was empty - e.g. "A,B,C," [Value 4 should be blank, not absent]
* 05-Oct-2003 KBM Increased size of "VALUE" and other columns
* 21-Apr-2004 KBM Converted to use Tally table, for speed, and to fix bug when using SPACE as delimiter
* Based on article: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1092
* Does NOT catch a blank final value
* 17-Jul-2004 KBM Fixed "final value blank" bug, based on Joe Celko's article
* http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=20675
* Does NOT catch single blank values with MULTI-character delimiters
*/
BEGIN
DECLARE @intLen int, -- Length of the source string
@intNewDelimiter int

IF DATALENGTH(@strDelimiter) > 1
BEGIN
-- Cannot safely handle BLANK fields with multi-character delimiter
-- Use single character delimiter instead
-- Find lowest character NOT used in @strSource
SELECT @intNewDelimiter = MIN(mb_int_ID)
FROM dbo.kk_MB_INT_Integer
WHERE @strSource NOT LIKE '%' + CHAR(mb_int_ID) + '%'
SELECT @strSource = REPLACE(@strSource, @strDelimiter, CHAR(@intNewDelimiter)),
@strDelimiter = CHAR(@intNewDelimiter)
END

SELECT @intLen = DATALENGTH(@strSource)

-- Surround string with delimiters to make processing easier
SELECT @strSource = @strDelimiter + @strSource + @strDelimiter

-- Split string using Tally table and Delimiters
INSERT @tblArray(Value)
SELECT
[Value] = COALESCE(
NullIf(
SUBSTRING
(
@strSource,
MAX(S1.mb_int_ID + 1),
(S2.mb_int_ID - MAX(S1.mb_int_ID + 1))
),
''),
@strBlank) -- Convert blank items to user-supplied value
FROM dbo.kk_MB_INT_Integer AS S1,
dbo.kk_MB_INT_Integer AS S2
WHERE SUBSTRING (@strSource , S1.mb_int_ID, 1) = @strDelimiter
AND SUBSTRING (@strSource , S2.mb_int_ID, 1) = @strDelimiter
AND S1.mb_int_ID < S2.mb_int_ID
AND S2.mb_int_ID <= DATALENGTH(@strSource) + 1
GROUP BY S2.mb_int_ID
HAVING COALESCE(
NullIf(
SUBSTRING
(
@strSource,
MAX(S1.mb_int_ID + 1),
(S2.mb_int_ID - MAX(S1.mb_int_ID + 1))
),
''),
@strBlank, 'XXX') -- Need to make NULL fail to match here, rather than "do nothing"!
<> '[DELETE]'
ORDER BY S2.mb_int_ID

RETURN
/*
-- Example:
DECLARE @strString varchar(8000)
SELECT @strString = '1,2,,4'
SELECT *
FROM dbo.KK_FN_DelimSplit_V2(@strString, ',', '[BLANK]')

-- ===== Create Tally Table (Run from here to marker below) ==== --
SET NOCOUNT OFF

CREATE TABLE dbo.kk_MB_INT_Integer
(
mb_int_ID int NOT NULL,
CONSTRAINT [PK_kk_MB_INT_Integer] PRIMARY KEY CLUSTERED
(
[mb_int_ID]
) WITH FILLFACTOR = 100 ON [PRIMARY]
)


DECLARE @v sql_variant
SET @v = N'List of integers for reference / joins'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'kk_MB_INT_Integer', NULL, NULL
SET @v = N'ID number'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'user', N'dbo', N'table', N'kk_MB_INT_Integer', N'column', N'mb_int_ID'

-- sp_indexoption cannot be executed within a transaction block
EXEC sp_indexoption 'dbo.kk_MB_INT_Integer', 'disallowrowlocks', TRUE
EXEC sp_indexoption 'dbo.kk_MB_INT_Integer', 'disallowpagelocks', TRUE

SET NOCOUNT ON

TRUNCATE TABLE kk_MB_INT_Integer

DECLARE @I int
SELECT @I = 1
WHILE @I <= 8000 -- this value should be the upper limit on the string length
BEGIN
INSERT dbo.kk_MB_INT_Integer(mb_int_ID) VALUES (@I)
SELECT @I = @I+1
END
SET NOCOUNT OFF
-- ===== Create Tally Table -- ** RUN TO HERE ** ==== --

*/
END
--================== KK_FN_DelimSplit_V2 ==================--
GO
PRINT 'Create function KK_FN_DelimSplit_V2 - DONE'
GO
--
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2005-06-05 : 06:42:23
http://www.mindsdoor.net/SQLTsql/ParseCSVString.html
and
http://www.mindsdoor.net/SQLTsql/f_GetEntryDelimiitted.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-09-30 : 02:46:43
To split a TEXT datatype that contains CSV or other delimited data see

http://weblogs.sqlteam.com/davidm/archive/2003/12/12/655.aspx

Kristen
Go to Top of Page

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-10-20 : 23:48:43
Hope you don't mind if I add a similar link:

http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2005-10-24 : 13:50:42
quote:
Originally posted by Kristen

To split a TEXT datatype that contains CSV or other delimited data see

http://weblogs.sqlteam.com/davidm/archive/2003/12/12/655.aspx

Kristen



Don't know if that's the thread I was involved in before (or if it copes with this) but charindex doesn't support text datatypes so it will convert the value to a string and could miss values.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-11-27 : 02:46:44
Splitting a Fixed Number of parts - either as a SELECT or an UPDATE - e.g. to get the separate parts into their own columns

(copied here for conveience from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58215&whichpage=2)

Here's the code to generate a SELECT and an UPDATE to "split" a fixed number of fields.

Note that this generates "blank string" values where there is a trailing delimiter and NO following value, and NULL values if a trailing delimiter (and its value, obviously!) are missing

Just change the parameters in green and run to generate the SQL code

-- Based on code by http://www.umachandar.com/technical/SQL70Scripts/Main10.htm
-- Modified by Kristen 2004/2005


DECLARE @pos int,
@numtokens int,
@field varchar(128),
@field2 varchar(128),
@strDelimiter varchar(1),
@previdx varchar(255),
@maxtokens int


-- Set these parameters as appropriate, and run down to "TEST RIG"
SELECT @maxtokens = 5, -- Number of tokens, per row, to be split
@field = 'MyCol', -- Name of Column to split
@strDelimiter = '|' -- Delimiter - e.g. CHAR(9) or ','


SELECT @field2 = @field + ' + ''' + @strDelimiter + '''' -- Shorthand! for @field plus delimiter

-- SELECT stuff
SELECT @numtokens = 0, -- Initialisation
@previdx = '0' -- charindex('|', @str, 1)
PRINT 'SELECT'
WHILE(@numtokens < @maxtokens)
BEGIN
PRINT CASE WHEN @numtokens = 0 THEN ' ' ELSE ', ' END
+ '[COLUMN_' + CONVERT(varchar(20), @numtokens+1) + '] = '
+ 'substring(' + @field2 + ', ' + CASE WHEN @numtokens = 0 THEN '0' ELSE @previdx END
+ ' + 1, charindex(''' + @strDelimiter + ''', ' + @field2 + ', '
+ CASE WHEN @numtokens = 0 THEN '0' ELSE @previdx END + ' + 1)'
+ ' - ' + CASE WHEN @numtokens = 0 THEN '0' ELSE @previdx END + ' - 1 )'
SELECT @previdx = ' charindex(''' + @strDelimiter + ''', ' + @field2
+ CASE WHEN @numtokens = 0 THEN '' ELSE ', ' + @previdx + ' + 1' END + ')',
@numtokens = @numtokens + 1
END
PRINT 'FROM dbo.MyTable'

-- UPDATE stuff
SELECT @numtokens = 0 -- Initialisation
PRINT 'UPDATE U'
PRINT 'SET'
WHILE(@numtokens < @maxtokens)
BEGIN
IF @numtokens = 0
BEGIN
PRINT CHAR(9) + ' @I' + CONVERT(varchar(20), @numtokens+1)
+ ' = CHARINDEX(''' + @strDelimiter + ''', ' + @field2 + ')'
PRINT CHAR(9) + ', [COLUMN_' + CONVERT(varchar(20), @numtokens+1)
+ '] = LEFT(' + @field + ', @I'
+ CONVERT(varchar(20), @numtokens+1) + '-1)'
END
ELSE
BEGIN
PRINT CHAR(9) + ', @I' + CONVERT(varchar(20), @numtokens+1)
+ ' = NullIf(CHARINDEX(''' + @strDelimiter + ''', '
+ @field2 + ', @I' + CONVERT(varchar(20), @numtokens)
+ '+1), 0)'
PRINT CHAR(9) + ', [COLUMN_' + CONVERT(varchar(20), @numtokens+1)
+ '] = SUBSTRING(' + @field + ', @I' + CONVERT(varchar(20), @numtokens)
+ '+1, @I' + CONVERT(varchar(20), @numtokens+1) + '-@I'
+ CONVERT(varchar(20), @numtokens) + '-1)'
END
SELECT @numtokens = @numtokens + 1
END
PRINT 'FROM dbo.MyTable AS U'



-- TEST RIG:
DECLARE @MyTable TABLE
(
MyCol varchar(50),
MyCol1 varchar(10),
MyCol2 varchar(10),
MyCol3 varchar(10),
MyCol4 varchar(10),
MyCol5 varchar(10)
)

INSERT INTO @myTable
SELECT 'AA1|BBB1|CCCC1|DDD1|EEEE1', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAA2|BBB2|CC2|DDD2|EEEE2', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAAAA3|BBB3|CCCC3|DDD3|EEEE3', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAA4|BBB4|CCCC4|DD4|EEEE4', Null,Null,Null,Null,Null UNION ALL
SELECT 'A5|BBB5|CCCC5|DDD5|E5', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAA6|BBB6|CCCC6|DDD6|EEEE6', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAA7|BBB7|CCCCC7|DDD7|EEEE7', Null,Null,Null,Null,Null UNION ALL
SELECT 'AA8|BBBBBBB8|CCCC8|DDD8|EEEE8', Null,Null,Null,Null,Null UNION ALL
SELECT 'AA9|BBBBBBB9|CCCC9|DDD9|', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAA|BBBBBBBA|CCCCA|DDDA', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAB|BBBBBBBB|CCCCB|', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAC|BBBBBBBC|CCCCC', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAD|BBBBBBBD|', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAE|BBBBBBBE', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAF|', Null,Null,Null,Null,Null UNION ALL
SELECT 'AAG', Null,Null,Null,Null,Null

DECLARE @I1 int,
@I2 int,
@I3 int,
@I4 int,
@I5 int

UPDATE U
SET
@I1 = CHARINDEX('|', MyCol + '|')
, [MyCol1] = LEFT(MyCol, @I1-1)
, @I2 = NullIf(CHARINDEX('|', MyCol + '|', @I1+1), 0)
, [MyCol2] = SUBSTRING(MyCol, @I1+1, @I2-@I1-1)
, @I3 = NullIf(CHARINDEX('|', MyCol + '|', @I2+1), 0)
, [MyCol3] = SUBSTRING(MyCol, @I2+1, @I3-@I2-1)
, @I4 = NullIf(CHARINDEX('|', MyCol + '|', @I3+1), 0)
, [MyCol4] = SUBSTRING(MyCol, @I3+1, @I4-@I3-1)
, @I5 = NullIf(CHARINDEX('|', MyCol + '|', @I4+1), 0)
, [MyCol5] = SUBSTRING(MyCol, @I4+1, @I5-@I4-1)
FROM @MyTable AS U


SELECT *
FROM @MyTable

Kristen
Go to Top of Page

sureshot
Yak Posting Veteran

72 Posts

Posted - 2005-12-02 : 14:57:38
The need for splitting is giving me worse performance for my SP's than dynamic SQL. It's making me think long and hard about converting everything to SP's. I've tried both of the following split functions (with the unlim function needing a Numbers table but strangely having less that 1/3 the plan cost of the non-unlim one). They are both far slower than just a dynamic SQL (SQL3) though but I have no other good way to pass a list of values to an SP.


-- SQL1 Cost .0625 (22%)
delete externalgraderesults
from externalgraderesults r
join dbo.udf_SplitUnlim(@externalresultIdList,',') idlist on r.externalgraderesult_id=idlist.value

-- SQL2 Cost .207 (73.25%)
delete externalgraderesults
from externalgraderesults r
join dbo.udf_Split(@externalresultIdList,',') idlist on r.externalgraderesult_id=idlist.value

-- SQL3 Cost .0133 (4.7%)
delete
from externalgraderesults
where externalgraderesult_id in (18,122,131)


-- 7998 bytes max (but faster)
CREATE FUNCTION dbo.udf_Split (@param varchar(7998)) RETURNS TABLE AS
RETURN(SELECT substring(',' + @param + ',', Number + 1,
charindex(',', ',' + @param + ',', Number + 1) - Number - 1)
AS Value
FROM Numbers
WHERE Number <= len(',' + @param + ',') - 1
AND substring(',' + @param + ',', Number, 1) = ',')


-- Unlimited size
CREATE FUNCTION dbo.udf_SplitUnlim(@list ntext,
@delim nchar(1) = N',')
RETURNS @t TABLE (str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL)
DECLARE @slice nvarchar(4000),
@textpos int,
@maxlen int,
@stoppos int

SELECT @textpos = 1, @maxlen = 4000 - 2
WHILE datalength(@list) / 2 - (@textpos - 1) >= @maxlen
BEGIN
SELECT @slice = substring(@list, @textpos, @maxlen)
SELECT @stoppos = @maxlen - charindex(@delim, reverse(@slice))
INSERT @slices (slice) VALUES (@delim + left(@slice, @stoppos) + @delim)
SELECT @textpos = @textpos - 1 + @stoppos + 2 -- On the other side of the comma.
END
INSERT @slices (slice)
VALUES (@delim + substring(@list, @textpos, @maxlen) + @delim)

INSERT @t (str, nstr)
SELECT str, str
FROM (SELECT str = ltrim(rtrim(substring(s.slice, N.Number + 1,
charindex(@delim, s.slice, N.Number + 1) - N.Number - 1)))
FROM Numbers N
JOIN @slices s ON N.Number <= len(s.slice) - 1
AND substring(s.slice, N.Number, 1) = @delim) AS x

RETURN
END
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-12-02 : 16:06:56
quote:
Originally posted by nr

quote:
Originally posted by Kristen

To split a TEXT datatype that contains CSV or other delimited data see

http://weblogs.sqlteam.com/davidm/archive/2003/12/12/655.aspx

Kristen



Don't know if that's the thread I was involved in before (or if it copes with this) but charindex doesn't support text datatypes so it will convert the value to a string and could miss values.



Notice that is uses PATINDEX as well.

It's used in 4 production systems and I haven't had any reports back about missing entries. Largest input string is about 30K.

DavidM

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-12 : 16:17:34
Using CROSS APPLY with a split function in SQL Server 2005, I was amazed at the difference in speed between inline TVFs and multiline TVFs.
So here's a split function using a recursive CTE, which is both inline and doesn't require a tally table:

ALTER FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
GO
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-12 : 17:07:16
This code is so simple that it doesn't really need to be in a function. It can't anyway, since it uses dynamic SQL.

It uses the replace function to turn the comma delimited string into an INSERT/SELECT num UNION ALL statement.

It works for a list of up to 250 integers delimited by commas, with no leading or trailing comma. My guess is that performance will be good, since it is a single insert statement.


-- Create temp table to test inserting values into
create table #t (num int)

-- Create a comma delimited string to test with
declare @str varchar(500)
select @str = '4,2,7,7834,45,24,45,77'

--------------------------------------------------------
---- Code to load the delimited string into a table ----
--------------------------------------------------------

-- Create insert for comma delimited values
declare @sql varchar(8000)
select @sql = 'insert into #t select '+
replace(@str,',',' union all select ')

-- Load values from comma delimited string into a table
exec ( @SQL )

--------------------------------------------------------
--------------------------------------------------------


-- Select values from temp table to show results
select * from #t

if object_id('tempdb..#t') is not null
begin drop table #t end



Edit:
I did some testing with comma delimited lists of various lengths on a fairly fast server (dual XEON 2.4). With up to 50 items, the elapsed time to load the string into the table was less than 0.003 seconds. With 250 items in the list, it took 0.110 second.






CODO ERGO SUM
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-01-12 : 17:39:34
interesting approach kernel

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-01-13 : 03:47:39
This is great stuff.
Could we not move the topic into the script library !?

rockmoose
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-30 : 02:52:36
quote:
...With up to 50 items, the elapsed time to load the string into the table was less than 0.003 seconds. With 250 items in the list, it took 0.110 second...


This code with 1374 values (on a single P4 1.8 gig machine with 1 gig ram)
---------------------------------------------------------------------------
DECLARE @List VARCHAR(8000)

SELECT @List = '48140,20657,58498,23214,52654,35893,46293,50857,43178,15358,12264,20740,22471,41027,54124,12077,41778,42742,31297,59558,15767,
29811,20197,49974,30946,39817,45633,20406,42798,47539,10247,22964,23436,23002,64896,28673,519,11792,19720,51803,2474,31876,53467,13988,41651,
31539,61574,30380,4680,41416,32298,29211,4932,39663,17711,2695,53396,31665,12623,49963,18363,40433,34560,51717,58325,64202,41670,34176,17481,
52041,21506,42409,36727,58633,25348,17062,3201,20082,32850,10795,17022,62619,237,49572,8698,29853,56878,12639,174,3646,352,906,43260,40648,
46480,29925,53300,20477,5506,21170,1654,26256,13617,54804,13308,57525,60672,16959,31783,49832,42510,29375,11160,49093,63172,62040,12613,30329,
55514,41024,39903,3851,56579,26818,41066,51959,58165,31309,56212,56662,47152,1829,13443,55987,24778,30559,335,44668,50715,56430,22070,55921,
35165,26688,25528,2190,43245,30648,59909,20047,41508,29823,39864,4605,41219,6346,48447,39550,37938,17588,20397,33193,8466,26268,48586,54978,
61352,27407,10517,38414,47999,19452,6161,60057,60822,1938,46309,59536,25270,10243,36480,29884,14176,29763,42165,59956,46268,32467,47864,30998,
11022,1794,26103,4005,46500,40854,46525,5852,62387,52350,30760,59745,16203,50949,2444,64783,42423,32179,21599,45821,34080,9392,36093,26023,
18670,12661,21848,7885,1007,62044,34166,54422,43304,42898,24027,35776,39571,62917,32213,4545,19268,12094,28774,50849,21825,25582,34353,22803,
52008,15796,34976,31740,10632,18471,28235,32108,54056,39046,40740,25605,10640,56747,59990,51101,53994,11648,48843,58598,30988,59659,42284,38430,
57696,44839,718,63917,27253,56039,42597,63663,21900,24794,10975,13379,37975,53493,24157,51290,5029,56864,26993,46991,16563,10442,47381,6164,
61768,9018,13732,16989,4914,12692,63130,22179,13104,50164,14878,46155,39550,50478,49530,59175,7145,1927,23120,58045,62023,21114,11847,24895,
47326,43448,55261,22505,26303,47170,38072,15787,32634,38156,30595,27392,32591,2780,9805,21224,25205,8704,49581,20396,42060,47670,44142,27557,
17116,8717,18909,4570,16474,61056,30963,8402,3772,24932,12706,62569,20919,64961,41644,64724,36479,35622,8591,26176,10572,42800,43250,16157,
25189,49495,1421,6627,54470,34037,33228,20002,46676,52340,35102,22302,3123,57357,35736,9673,38055,4787,4833,35257,15576,12549,54459,63430,
21994,55555,13250,7737,4720,15162,30590,7223,28704,54516,38034,36693,38110,22523,43552,11427,47079,42744,17162,42154,48799,22590,22695,18274,
2197,12790,53432,61019,60994,48877,17957,29689,31244,31276,3382,44449,30604,31716,39664,18341,54899,17012,4953,22545,10500,27082,47623,46012,
50355,54417,42875,34001,64956,42702,33244,47070,34314,21875,59159,46125,2884,2909,27886,24004,470,12848,15175,57425,51115,34386,51730,10059,
16361,32821,59434,11421,27565,48927,57457,25647,50271,34572,9023,35187,3004,11686,44674,41837,27200,29626,44629,7217,14985,35156,7493,25450,
14336,34760,50750,34545,23310,36271,23606,6265,22009,8132,787,24166,10138,28418,58766,57088,33531,37491,22493,28841,1085,59926,22172,54255,
22583,34563,30849,41227,26752,5940,14151,34218,60712,52233,31798,6697,51257,15183,38581,56414,41088,31212,4389,2395,49993,9379,13350,62655,
24449,41643,25125,26222,19944,22803,20855,62100,13729,7647,49211,29744,3522,23763,41877,22468,4278,64463,63606,29262,42140,58002,9081,16095,
18874,18878,353,41573,16920,48713,36772,30776,54156,45707,23986,16548,43046,22627,58781,23405,30920,34493,993,48145,30480,39142,37345,45858,
38948,14366,44018,49051,29779,18469,4676,29544,105,25737,62088,24099,15408,10642,9989,15739,1084,870,11539,12337,62223,45445,36461,38767,6818,
38108,30726,23925,14119,56332,32512,25204,29384,1525,11301,12385,20651,23746,57719,6803,60412,47150,12117,2324,24178,11530,15338,19960,44650,
4650,57252,32115,49655,10954,19060,22705,16499,54794,14865,52352,30178,23219,23564,42435,38081,62657,17567,19739,42707,12612,44026,15608,19041,
59985,51807,57642,2523,38643,60679,40238,34712,15131,38362,1604,8151,60214,13212,18501,1163,22820,10857,41067,54976,13997,43211,42760,46907,
41333,9595,5705,60597,50718,4204,32655,16879,39427,44335,54766,33892,21411,23855,26602,51646,12797,22151,46903,4833,51972,41061,62881,10482,
38046,43297,50713,50863,44604,25235,19030,4004,49817,54246,547,52017,20177,48889,63964,36929,53201,48432,65036,22232,1136,26024,53259,63836,
50672,5239,30507,8924,37226,65207,58378,12092,19760,31433,38580,31014,14881,49650,65479,42636,14859,28556,43490,40741,51506,3977,35074,40606,
58279,45496,13154,55321,41791,19393,29253,34963,9863,62359,23571,64394,33739,8592,18682,43579,20426,16525,19264,51291,38648,45250,28148,12296,
22920,15112,51873,2589,39646,49371,42734,48684,4279,38994,8238,31755,4269,40467,34997,12306,3833,15586,35702,23311,20071,35142,36630,606,39467,
23304,23832,10572,57603,25603,53563,61716,62631,52033,31672,14617,16758,34092,21687,30280,47039,28908,11112,11687,40465,29949,45544,5549,11091,
39825,25365,48736,59362,32526,25790,47371,49224,63875,46952,27648,51075,62992,32679,8781,25975,32768,22599,18354,49118,22254,27732,24546,55181,
51734,63135,31800,20876,33730,44096,28487,23397,61589,21794,13118,3260,35985,16651,36597,39349,6806,60409,46108,34716,2970,5878,22999,26442,
43293,39148,41395,42941,37415,51674,20531,11619,59250,34939,35214,48717,65512,24676,65304,65298,53598,34902,42062,40583,5614,53169,29841,62066,
32833,11382,2399,37655,5132,56984,42155,33074,20523,19122,22778,61785,14952,14798,41926,34204,48531,34384,27291,31659,40954,25675,26245,50292,
18261,8567,35287,12374,56498,23143,42552,27328,23451,26437,2454,33270,10655,44330,61050,39821,42408,9052,6474,50433,24363,4640,63739,33909,
20056,13325,18866,4638,17087,29477,5272,42307,28951,35369,25153,33542,21179,34131,31697,3758,13845,11148,24029,1778,54906,34042,43883,7304,
36597,35,20536,26404,54716,57770,39490,48634,4874,17826,14565,9714,8775,36099,19628,19274,7082,22101,44787,15268,890,8842,13311,38372,56534,
45378,48946,29516,35917,59003,56099,26007,15964,25915,9396,5826,29786,17528,13493,22402,4424,57524,55715,55952,15378,46813,41793,52169,35267,
33047,6049,41355,13761,12769,53503,41717,364,39952,27127,9082,18802,5276,4564,43661,45486,46179,35678,40695,64682,15596,50386,2301,1099,18590,
7485,58143,1080,16784,17516,47023,9561,23642,18128,4330,49731,1904,912,20597,48319,12137,47836,44280,19102,57819,12782,11217,33607,62977,62972,
60781,59660,11955,5102,19477,29952,6869,43405,59785,52105,23466,11823,14328,3216,47423,16260,32802,6182,43724,25613,24610,15038,2839,8174,42775,
36959,3209,39458,37711,31823,5796,2269,5454,24794,29945,62945,43823,19705,21764,4003,41217,802,22181,8178,9838,2550,45681,27034,31406,28832,1707,
10934,26990,26347,49658,16033,10285,3927,46291,37667,13427,52224,17043,31052,27247,53642,13277,34813,17446,61633,43916,42242,55875,26647,34044,
2378,25010,55856,3719,62976,3704,60749,32704,14220,2831,20650,21586,8744,50131,3782,5910,35419,44788,51707,22017,38230,51853,13606,15108,13028,
4137,51482,5704,16906,40276,41144,23244,21423,35597,20831,5666,52732,26261,58525,39337,57454,47287,8474,27670,31217,61810,42422,57310,57710,
63390,536,21609,47257,5997,2660,49435,64846,15946,57879,16030,8707,62999,33743,7712,15318,3868,30036,25908,53445,47326,10151,7450,55420,47317,
44446,53536,62616,64905,22850,6515,13172,1872,1597,41657,30386,23610,29017,62404,22310,54102,58415,32873,4987,20649,28813,26354,9749,29386,61736,
40524,50944,23077,61724,49368,19045,26966,54686,39504,63426,21023,64393,46255,3530,24386,26473,65050,10528,2877,11322,52375,60032,16385,52254,
24555,35964,49983,44036,38770,61068,65272,8111,12168,15360,1848,27399,62640,14768,663,19020,44574,40952,45422,22829,21244,43901,60351,7423,4527,
52666,36379,64039,2241,14624,64746,39850,12549,31067,63411,23819,21650,19735,15780,54991,23913,14304,1993,23874,34848,59841,9255,1921,42515,
61292,53664,11141,29928,61218,61484,18978,59204,16314,54182,25147,50478,32753,1157,16703,11543,64003,62905,40819,27562,11674,30798,46372,59448,
11729,42063,49181,38359,19966,57807,27123,55981,5787,8273,8543,45318,4813,4958,54679,54383,4873,63315,974,11223,16200,32777,37284,27554,9745,
37556,42067,10325,43202,32778,53596,62273,42683,5893,43136,60737,20967,56205,34396,37284,55267,61410,12322'

select 1 + len(@list) - len(replace(@list, ',', '')) 'items in list', len(@list) 'string length'

declare @t table (r varchar(6))

declare @start datetime

select @start = getdate()

insert @t
SELECT SUBSTRING(',' + @List + ',', w.i + 1, CHARINDEX(',', ',' + @List + ',', w.i + 1) - w.i - 1) value
FROM (
SELECT v0.n + v1.n + v2.n + v3.n i
FROM (
SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
)v0,
(
SELECT 0 n UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 UNION ALL SELECT 64 UNION ALL SELECT 80 UNION ALL SELECT 96 UNION ALL SELECT 112 UNION SELECT 128 UNION ALL SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
) v1,
(
SELECT 0 n UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768 UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536 UNION ALL SELECT 1792 UNION SELECT 2048 UNION ALL SELECT 2304 UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840
) v2,
(
SELECT 0 n UNION ALL SELECT 4096
) v3
) w
WHERE w.i = CHARINDEX(',', ',' + @List + ',', w.i) AND w.i < LEN(',' + @List)

select datediff(ms, @start, getdate()) milliseconds
---------------------------------------------------------------------------
1374 values took 93 ms, 14.8 values/ms.
255 values took 13 ms, 19.6 values/ms.
50 values took 0 ms. (Wasn't measurable with DATEDIFF ms!)
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-03 : 11:22:05
I somehow stumbled on this thread. I've been looking to get an understanding of when to use CTE's ...
DECLARE @List VARCHAR(8000)

select
@list = coalesce(@list+',','')+ char(round((rand(convert(binary(4),newid())) * 25),0)+97)
from
dbo.numbers
where
n < 3999

select 1 + len(@list) - len(replace(@list, ',', '')) 'items in list', len(@list) 'string length'

declare @t table (r varchar(6))

declare @start datetime

select @start = getdate()

insert @t
SELECT SUBSTRING(',' + @List + ',', w.i + 1, CHARINDEX(',', ',' + @List + ',', w.i + 1) - w.i - 1) value
FROM (
SELECT v0.n + v1.n + v2.n + v3.n i
FROM (
SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
)v0,
(
SELECT 0 n UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 UNION ALL SELECT 64 UNION ALL SELECT 80 UNION ALL SELECT 96 UNION ALL SELECT 112 UNION SELECT 128 UNION ALL SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
) v1,
(
SELECT 0 n UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768 UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536 UNION ALL SELECT 1792 UNION SELECT 2048 UNION ALL SELECT 2304 UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840
) v2,
(
SELECT 0 n UNION ALL SELECT 4096
) v3
) w
WHERE w.i = CHARINDEX(',', ',' + @List + ',', w.i) AND w.i < LEN(',' + @List)
select datediff(ms, @start, getdate()) milliseconds
delete @t
select @start = getdate()
insert @t
select s from dbo.Split(',',@list)
option(MAXRECURSION 4000)
select datediff(ms, @start, getdate()) milliseconds

 
Peso's method - around 60ms
AF's CTE - usually under 13ms (unmeasurable)

Interesting

Jay White
Go to Top of Page

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-12-24 : 17:08:06
Hi,
http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/treatyourselfListing_01.txt
The script there works only if provide the csv string while calling the Fn_Split function. Is there a way to apply the function to csv file by providing its path like C:\myFolder\myCSVFile instead of entering the whole csv string in the script.
Also, one more question: the script creates only one column for the real data.
How about if i have a csv file with two informations: col1 and col2 whose contents get repeated all over the csv file. how can that produce a table with two columns: col1 and col2?
Here was my problem:
I tried this CSV example:
John,Doe,120 jefferson st.,Riverside, NJ, 08075
Jack,McGinnis,220 hobo Av.,Phila, PA,09119
"John ""Da Man""",Repici,120 Jefferson St.,Riverside, NJ,08075
Stephen,Tyler,"7452 Terrace ""At the Plaza"" road",SomeTown,SD, 91234
,Blankman,,SomeTown, SD, 00298
"Joan ""the bone"", Anne",Jet,"9th, at Terrace plc",Desert City,CO,00123

But I got only a table with one column instead of 5 columns
Is that normal. I used the this function :
http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/treatyourselfListing_01.txt


Thanks a lot for your help.
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-02-05 : 01:12:06
quote:
Originally posted by Arnold Fribble

Using CROSS APPLY with a split function in SQL Server 2005, I was amazed at the difference in speed between inline TVFs and multiline TVFs.
So here's a split function using a recursive CTE, which is both inline and doesn't require a tally table:

ALTER FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
GO




Is recursion still limited to only 32 levels in SQL Server 2005? If it is, BOOOOOM on the 33 parameter that needs to be split... (I could be wrong... might be different on CTE's which I've not had the pleasure to work with, yet.)

--Jeff Moden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-05 : 03:24:36
How does that compare to the parselist function here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033 ?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2007-05-08 : 06:25:59
Peso: I had reason to revisit this today, and the performance of my Celko-based approach is AWFUL!

In trying yours I couldn't get a blank final entry [I know, bit of a pedantic edge condition!!] processed, e.g.

DECLARE @List VARCHAR(8000)

SELECT @List = '48140,20657,58498,'

select 1 + len(@list) - len(replace(@list, ',', '')) 'items in list', len(@list) 'string length'

declare @t table (r varchar(6))

insert @t
SELECT SUBSTRING(',' + @List + ',', w.i + 1, CHARINDEX(',', ',' + @List + ',', w.i + 1) - w.i - 1) value
FROM (
SELECT v0.n + v1.n + v2.n + v3.n i
FROM (
SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
UNION ALL SELECT 6 UNION ALL SELECT 7 UNION SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
)v0,
(
SELECT 0 n UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 UNION ALL SELECT 64 UNION ALL SELECT 80
UNION ALL SELECT 96 UNION ALL SELECT 112 UNION SELECT 128 UNION ALL SELECT 144 UNION ALL SELECT 160
UNION ALL SELECT 176 UNION ALL SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
) v1,
(
SELECT 0 n UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768 UNION ALL SELECT 1024
UNION ALL SELECT 1280 UNION ALL SELECT 1536 UNION ALL SELECT 1792 UNION SELECT 2048 UNION ALL SELECT 2304
UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584
UNION ALL SELECT 3840
) v2,
(
SELECT 0 n UNION ALL SELECT 4096
) v3
) w
WHERE w.i = CHARINDEX(',', ',' + @List + ',', w.i) AND w.i < LEN(',' + @List)

SELECT * FROM @t

the 4th entry is missing from the results. I reckon that the "fix" is

WHERE w.i = CHARINDEX(',', ',' + @List + ',', w.i) AND w.i <= LEN(',' + @List)

does that look right?

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-08 : 07:42:27
Yes, that is the solution.
Thanks for the update!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -