SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Best split function
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/08/2007 :  12:32:39  Show Profile  Reply with Quote
Is there a way to get your dynamically created Tally table to provide the split-values in order?

I replaced your dynamical-tally-table with my "permanent" Tally table, and could Order By that to get the values in order.

But the performance of a permanent table was no better, so strikes me that it is easier for code I want to use on client sites to have a no-table-required method. But I do have a need for ordering of the Values.

Thanks

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 05/08/2007 :  12:45:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
A trailing ORDER BY seems to work.
WHERE w.i = CHARINDEX(',', ',' + @List + ',', w.i) AND w.i <= LEN(',' + @List)
ORDER BY w.i


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/08/2007 :  12:52:13  Show Profile  Reply with Quote
Hmmm .. tried that and it didn't work. I'll go back and recheck my code, I expect I've Rogered something!

EDIT: Yeah, for some reason my Pea-sized brain expected the Values to be in ascending order, which would, of course, make the need for Item-ordering redundant.

Brain now back in gear, thanks!

Kristen

Edited by - Kristen on 05/08/2007 13:04:49
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/08/2007 :  13:52:09  Show Profile  Reply with Quote
Revised version of my function, earlier in this thread, using Peso's code. This is MUCH faster than the original I posted.

--
PRINT 'Create function KK_FN_DelimSplit_V3'
PRINT '*** NOTE: KK_FN_DelimSplit_V3 requires a Tally table.  See the source code for details ***'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[KK_FN_DelimSplit_V3]') AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION [dbo].[KK_FN_DelimSplit_V3]
GO

CREATE FUNCTION dbo.KK_FN_DelimSplit_V3
(
	@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 NOTE: This will leave gaps in [Item] sequence
)
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_V3	Split a string based on delimiter
 *
 * Example:	SELECT Item, Value FROM dbo.KK_FN_DelimSplit_V3('A,B,C', ',', NULL)
 *
 * Returns:
 *
 *	Resultset of Item/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
 * 08-May-2007 KBM  V3 - Updated with code from Peso (much faster)
 *		    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
 */
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
-- NOTE: This needs reprogramming if permanent Tally table not available
		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) + DATALENGTH(@strDelimiter)

	-- 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, 
						w.mb_int_ID + 1, 
						CHARINDEX(@strDelimiter, @strSource, w.mb_int_ID + 1) - w.mb_int_ID - 1
					),
					 ''), 
				@strBlank)	-- Convert blank items to user-supplied value
--
/**
-- NOTE: This can be used INSTEAD of a permanent Tally table
	FROM
	(
		SELECT v0.n + v1.n + v2.n + v3.n AS mb_int_ID
		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 ALL 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 ALL 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 ALL 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
**/
	FROM	dbo.kk_MB_INT_Integer AS w
--
	WHERE	    w.mb_int_ID = CHARINDEX(@strDelimiter, @strSource, w.mb_int_ID) 
		AND w.mb_int_ID <= @intLen

	IF @strBlank = '[DELETE]'
	BEGIN
		DELETE	D
		FROM	@tblArray AS D
		WHERE	[Value] = '[DELETE]'
	END

	RETURN
/*
-- Example:
DECLARE	@strString varchar(8000)
SELECT	@strString = '1,2,,4'
SELECT	* 
FROM	dbo.KK_FN_DelimSplit_V3(@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_V3 ==================--
GO
PRINT 'Create function KK_FN_DelimSplit_V3 - DONE'
GO
--

EDIT: 05Jul2007 Changed "UNION" to "UNION ALL"

Kristen

Edited by - Kristen on 07/05/2007 06:14:35
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/08/2007 :  13:59:10  Show Profile  Reply with Quote
I ran some tests based on 6, 20 and 1,372 items to be split.

6 items:
MindsDoor, 7thNight, PesoParse, PesoParseList and KK_FN_DelimSplit_V3 very fast (average 0-2ms)
Peso and my original "slower" at 22-26ms

20 items:
MindsDoor, 7thNight, PesoParse and KK_FN_DelimSplit_V3 very fast (average 2-5ms)
PesoParseList 7ms
Peso and my original "slower" at 24-30ms

1,372 items:
57ms Peso
90ms KK_FN_DelimSplit_V3
153ms MindsDoor
157ms PesoParse
168ms 7thNight
171ms PesoParseList
2,113ms my original

EDIT: all were wrapped in a function, except for "Peso" which was inline SQL

Kristen

Edited by - Kristen on 08/29/2007 10:14:39
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/08/2007 :  14:46:58  Show Profile  Reply with Quote
Did you clean the buffers and the cache between each test?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 05/08/2007 :  14:51:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And while you are already timetesting...
How long time do fnParseList take?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/08/2007 :  15:07:15  Show Profile  Reply with Quote
"Did you clean the buffers and the cache between each test?"

Nah, in the Real World I'd be looking for something that could cache well .......... </thud!>

"How long time do fnParseList take?"

See above

Kristen
Go to Top of Page

saypaulsay
Starting Member

1 Posts

Posted - 08/23/2007 :  22:30:25  Show Profile  Reply with Quote
Seventhnight - I like ths simplicity of you function,

However I noticed that if the delimiter (@SplitOn) is greater than one character the results anr't correct.

I beleive the following change is required.

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

Edited by - saypaulsay on 08/23/2007 22:33:24
Go to Top of Page

Amberite
Starting Member

11 Posts

Posted - 08/29/2007 :  09:27:17  Show Profile  Reply with Quote
Here's mine. From what I can see it's fast, but I've never profiled it so don't know exactly how fast. It's nice and simple tho :)

CREATE FUNCTION [dbo].[Split](
	@String nvarchar(4000), 
	@Delimiter char(1)
)
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
    SET @String = LTRIM(RTRIM(@String))
    IF LEN(@STRING) = 0 
        RETURN
    DECLARE @INDEX INT
    DECLARE @SLICE nvarchar(4000)
    SELECT @INDEX = 1
    WHILE @INDEX !=0 BEGIN	
        	SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
        	IF @INDEX !=0
        		SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
        	ELSE
        		SELECT @SLICE = @STRING
        	INSERT INTO @Results(Items) VALUES(@SLICE)
        	SELECT @STRING = LTRIM(RIGHT(@STRING,LEN(@STRING) - @INDEX))
        	IF LEN(@STRING) = 0 BREAK
    END
    RETURN
END
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/29/2007 :  09:54:33  Show Profile  Reply with Quote
"From what I can see it's fast, but I've never profiled it so don't know exactly how fast"

Fails to return blank item

6 items: 3 ms (fast)
20 items: 16 ms (slow-ish)

1,372 items: 126 ms - but only returned 685 items due to Nvarchar use

Changing the code to use Varchar gives 184 ms runtime. (Very slow)

Which basically confirms my earlier post, which is that for very few items in the list there is not much issue which method you use, but for potentially large lists more sophistication is required.

My Split function had been tucked away, used for years, used whenever we needed to Split anything, and then I discovered it was a performance bottleneck by accident once it had gotten used for splitting large lists!

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 08/29/2007 :  09:58:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Kristen, do you have the time to update the timing list above, with the newer algorithm for my fnParseList function?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/29/2007 :  10:15:23  Show Profile  Reply with Quote
No problem, still had the code open in Query Analyser. See re-edited reply above

Kristen
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 08/29/2007 :  10:18:30  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Here is a new twist....


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 @start datetime

Declare @sampleData varchar(8000)
Set @SampleData = 'slkj,dskjhd,sdkjhds,dshd,dweh2,23,323ids,33j4hkds,3j4hk3ds,3j4hko3s,dr3oisd,js,3io3djoe,ejroeifsjhdf,dsfo'
Set @SampleData = '1,2,34,56,7,8,99,123,4,56,7890,'
Set @SampleData = @List

select @start = getdate()


Declare @steps table (step int, data varchar(7950))

Declare @step int
Set @step = 0

If Charindex(',',@sampleData, convert(int,len(@sampleData)/2-len(@sampleData)/2*.25)) > 0
	Insert Into @steps
	Select @step, ltrim(rtrim(Substring(@sampleData,1,Charindex(',',@sampleData,convert(int,len(@sampleData)/2-len(@sampleData)/2*.25))-1))) 
	Union All
	Select @step, ltrim(rtrim(Substring(@sampleData,Charindex(',',@sampleData,convert(int,len(@sampleData)/2-len(@sampleData)/2*.25))+Len(','),8000)))

Set @step = @step + 1

While (@step < 100 and exists(Select * From @steps Where step = @step-1 and data like '%'+','+'%'))
Begin
	Insert Into @steps
	Select 
		step = @step,
		data = ltrim(rtrim(Substring(data,1,Charindex(',',data,convert(int,len(data)/2-len(data)/2*.25))-1)))
	From @steps 
	Where step = @step-1
	and Charindex(',',data,convert(int,len(data)/2-len(data)/2*.25)) > 0
	Union All
	Select 
		step = @step,
		data = ltrim(rtrim(Substring(data,Charindex(',',data,convert(int,len(data)/2-len(data)/2*.25))+Len(','),8000)))
	From @steps 
	Where step = @step-1
	and Charindex(',',data,convert(int,len(data)/2-len(data)/2*.25)) > 0

	Set @Step = @Step + 1
End

Select * 
From @steps
Where data not like '%'+','+'%'
Order By 2

select datediff(ms, @start, getdate()) milliseconds


Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 09/03/2007 :  04:16:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Time to add Ranganath's SQL Server 2005 specific now?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88773



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/03/2007 :  08:07:11  Show Profile  Reply with Quote
"Time to add Ranganath's SQL Server 2005 specific now?"

performance is terrible on long lists - several seconds instead of 100ms or so for previous methods

I thought I saw a CROSS JOIN solution posted somewhere that was purported to be fast?

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 09/03/2007 :  08:23:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by Kristen

-- 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
What is the lifetime expectancy for those settings?
  • Always
  • Reset when service is restarted



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/03/2007 :  08:29:20  Show Profile  Reply with Quote
"What is the lifetime expectancy for those settings?"

No idea mate. I create that Numbers Tally table years ago ... they might still be in force, on the original server I used, for all I know!

I do remember there being some problem creating the Tally table without them ... can't quite understand why 'coz its just "insert 8,000 rows", which is hardly that special ...

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30115 Posts

Posted - 09/03/2007 :  08:39:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What I understand from Books Online, you tell SQL Server to never use any locks on this "table" when referencing it in a JOIN or any other type.
Which is now deprecated with the ALTER INDEX statement, according to Books Online.


E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 09/03/2007 08:42:59
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/03/2007 :  09:08:22  Show Profile  Reply with Quote
"never use any locks on this "table""

Ah ... well that does seem appropriate. The Tally entries aren't going to change - although I could extend the number of rows in the table, in the future, of course ...

Kristen
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000