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
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

SamC
White Water Yakist

USA
3460 Posts

Posted - 06/03/2005 :  09:10:33  Show Profile  Reply with Quote
http://msdn.microsoft.com/library/en-us/dnsqlmag01/html/treatyourselfListing_01.txt

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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/03/2005 :  09:28:01  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 06/05/2005 :  03:41:12  Show Profile  Reply with Quote
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
--

Edited by - Kristen on 06/05/2005 03:45:56
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 06/05/2005 :  06:42:23  Show Profile  Visit nr's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 09/30/2005 :  02:46:43  Show Profile  Reply with Quote
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

USA
95 Posts

Posted - 10/20/2005 :  23:48:43  Show Profile  Visit rfrancisco's Homepage  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 10/24/2005 :  13:50:42  Show Profile  Visit nr's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 11/27/2005 :  02:46:44  Show Profile  Reply with Quote
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

Edited by - Kristen on 07/05/2007 07:23:15
Go to Top of Page

sureshot
Yak Posting Veteran

72 Posts

Posted - 12/02/2005 :  14:57:38  Show Profile  Reply with Quote
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

Edited by - sureshot on 12/02/2005 14:58:34
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 12/02/2005 :  16:06:56  Show Profile  Reply with Quote
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

United Kingdom
1961 Posts

Posted - 01/12/2006 :  16:17:34  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 01/12/2006 :  17:07:16  Show Profile  Reply with Quote
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

Edited by - Michael Valentine Jones on 01/12/2006 18:16:59
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 01/12/2006 :  17:39:34  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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

Sweden
3279 Posts

Posted - 01/13/2006 :  03:47:39  Show Profile  Reply with Quote
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

Sweden
30115 Posts

Posted - 05/30/2006 :  02:52:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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!)

Edited by - SwePeso on 05/30/2006 03:28:27
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/03/2006 :  11:22:05  Show Profile  Reply with Quote
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 - 12/24/2006 :  17:08:06  Show Profile  Reply with Quote
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.

Edited by - rtutus on 12/24/2006 17:27:42
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 02/05/2007 :  01:12:06  Show Profile  Reply with Quote
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

Sweden
30115 Posts

Posted - 02/05/2007 :  03:24:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 05/08/2007 :  06:25:59  Show Profile  Reply with Quote
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

Sweden
30115 Posts

Posted - 05/08/2007 :  07:42:27  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes, that is the solution.
Thanks for the update!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
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.19 seconds. Powered By: Snitz Forums 2000