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
 General SQL Server Forums
 Script Library
 Having trouble splitting a list to a resultset?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 05/29/2006 :  20:35:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Ok, run this...

SELECT * FROM dbo.fnListSplit('a,b,c,d,e,f', ',')

-------------------------------------------------------------
CREATE FUNCTION dbo.fnListSplit
(
@List VARCHAR(8000),
@Delimiter VARCHAR(2)
)
RETURNS @Resultset TABLE (i SMALLINT IDENTITY(0, 1), x VARCHAR(8000))
AS

BEGIN
INSERT @Resultset
(
x
)
SELECT SUBSTRING(@Delimiter + @List + @Delimiter, w.i + 1, CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.i + 1) - w.i - 1)
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(@Delimiter, @Delimiter + @List + @Delimiter, w.i) AND w.i < LEN(@Delimiter + @List)
ORDER BY w.i

RETURN
END

Edited by - SwePeso on 05/29/2006 20:52:30

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/30/2006 :  00:53:28  Show Profile  Reply with Quote
If you are going to do that more than once in a blue moon you'd be better off having a permenant Tally Table

See also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Best+split+functions

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 05/30/2006 :  02:24:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes, you're right. This was the just easy one

For computing a complete table in one step, use this (assuming ';' is the multi-valued data delimeter... Also this code is optimized to have at most 256 multi-values in one field.
---------------------------------------------------------------------
CREATE TABLE #T (RowID INT, zPos SMALLINT, xText VARCHAR(30))

INSERT INTO #T (RowID, xText)
SELECT SourceTable.RowID, w.i,
SUBSTRING(';' + SourceTable.SourceColumn + ';', w.i + 1, CHARINDEX(';', ';' + SourceTable.SourceColumn + ';', w.i + 1) - w.i - 1)
FROM SourceTable, (
SELECT v0.n + v1.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) w
WHERE w.i = CHARINDEX(';', ';' + SourceTable.SourceColumn + ';', w.i) AND w.i < LEN(';' + SourceTable.SourceColumn)
AND SourceTable.WhatEver BETWEEN 6 AND 1232

SELECT * FROM #T ORDER BY RowID, zPos

Edited by - SwePeso on 06/09/2006 03:23:56
Go to Top of Page

Igor2004
More clever than you

Canada
78 Posts

Posted - 06/08/2006 :  11:17:51  Show Profile  Visit Igor2004's Homepage  Reply with Quote
-- Author:  Igor Nikiforov,  Montreal,  EMail: udfunctions@gmail.com 
 -- GETALLWORDS2() User-Defined Function Inserts the words from a string into the table.
 -- GETALLWORDS2(@cString[, @cStringSplitting])
 -- Parameters
 -- @cString  nvarchar(4000) - Specifies the string whose words will be inserted into the table @GETALLWORDS2. 
 -- @cStringSplitting nvarchar(256) - Optional. Specifies the string used to separate words in @cString.
 -- The default delimiter is space.
 -- Note that GETALLWORDS2( ) uses  @cStringSplitting as a single delimiter. 
 -- Return Value table 
 -- Remarks GETALLWORDS2() by default assumes that words are delimited by space. If you specify another string as delimiter, this function ignores spaces and uses only the specified string.
 -- Example
 -- declare @cString nvarchar(4000), @nIndex smallint 
 -- select @cString = 'We hold these truths to be self-evident, that all men are created equal, that they are endowed by their Creator with certain unalienable Rights, that among these are Life, Liberty and the pursuit of Happiness.', @nIndex = 30
 -- select WORD from dbo.GETALLWORDS2(@cString, default) where WORDNUM = @nIndex  -- Displays 'Liberty'
 -- select top 1 WORDNUM from dbo.GETALLWORDS2(@cString, default) order by WORDNUM desc  -- Displays 35
 -- See Also GETWORDNUM() , GETWORDCOUNT() ,  GETALLWORDS()  User-Defined Functions   
CREATE function GETALLWORDS2  (@cString nvarchar(4000), @cStringSplitting  nvarchar(256) = ' '  )   -- if no break string is specified, the function uses space to delimit words.
returns  @GETALLWORDS2  table (WORDNUM  smallint, WORD nvarchar(4000), STARTOFWORD smallint, LENGTHOFWORD  smallint)
    begin
        declare @k smallint,   @BegOfWord smallint,  @wordcount  smallint,  @nEndString smallint,  @nLenSrtingSplitting smallint, @flag bit

        select   @cStringSplitting = isnull(@cStringSplitting, space(1)) ,
                    @cString = isnull(@cString, '') ,
                    @BegOfWord = 1,   @wordcount = 1,  @k = 0 , @flag = 0,
                    @nEndString =  1+  datalength(@cString) /(case SQL_VARIANT_PROPERTY(@cString,'BaseType') when 'nvarchar' then 2  else 1 end),
                    @nLenSrtingSplitting =  datalength(@cStringSplitting) /(case SQL_VARIANT_PROPERTY(@cStringSplitting,'BaseType') when 'nvarchar' then 2  else 1 end)   -- for unicode

       while  1 > 0 
          begin
             if  @k - @BegOfWord  >  0  
                 begin
                      insert into @GETALLWORDS2 (WORDNUM,  WORD, STARTOFWORD, LENGTHOFWORD)    values( @wordcount,  substring(@cString,  @BegOfWord , @k - @BegOfWord ) , @BegOfWord,  @k - @BegOfWord)
                      select  @wordcount = @wordcount + 1,  @BegOfWord = @k 
                 end 

             if  @flag  = 1 
                 break

             while charindex( substring(@cString, @BegOfWord, @nLenSrtingSplitting) COLLATE Latin1_General_BIN, @cStringSplitting COLLATE Latin1_General_BIN) > 0 --  skip break strings, if any     
                 set  @BegOfWord  = @BegOfWord +  @nLenSrtingSplitting

             select   @k  = charindex(@cStringSplitting  COLLATE Latin1_General_BIN, @cString COLLATE Latin1_General_BIN, @BegOfWord)   

             if  @k = 0 
                select   @k  =  @nEndString,  @flag  = 1
          end

       return 
    end
GO
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 08/04/2006 :  08:33:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
CREATE FUNCTION dbo.fnSplitDelimitedString
(
	@Text VARCHAR(8000),
	@Delimiter VARCHAR(8000)
)
RETURNS @Parts TABLE
	(
		i SMALLINT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
		Part VARCHAR(8000)
	)
AS

BEGIN
	DECLARE	@LastIndex SMALLINT,
		@NextIndex SMALLINT

	IF @Text IS NULL OR DATALENGTH(@Text) = 0
		RETURN

	IF @Delimiter IS NULL
		SELECT	@Delimiter = ';'

	SELECT	@LastIndex = 0,
		@NextIndex = 1

	WHILE @NextIndex > 0
		BEGIN
			SELECT	@NextIndex = CHARINDEX(@Delimiter, @Text, @LastIndex + 1)

			INSERT	@Parts
				(
					Part
				)
			SELECT	CASE
					WHEN @NextIndex = 0 THEN SUBSTRING(@Text, @LastIndex + 1, DATALENGTH(@Text) - @LastIndex)
					ELSE SUBSTRING(@Text, @LastIndex + 1, @NextIndex - @LastIndex - 1)
				END

			SELECT	@LastIndex = @NextIndex
		END

	RETURN
END



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 08/04/2006 :  08:33:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
CREATE FUNCTION dbo.fnSplitFixedString
(
	@Text VARCHAR(8000),
	@PartLength SMALLINT,
	@WantedStart SMALLINT,
	@WantedLength SMALLINT
)
RETURNS @Parts TABLE
	(
		i SMALLINT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
		Part VARCHAR(8000)
	)
AS

BEGIN
	DECLARE	@Index SMALLINT

	IF @PartLength < 1 OR @PartLength IS NULL
		SELECT @PartLength = 1

	IF @PartLength > DATALENGTH(@Text)
		SELECT @PartLength = DATALENGTH(@Text)

	IF @WantedStart < 1 OR @WantedStart IS NULL
		SELECT @WantedStart = 1

	IF @WantedStart > @PartLength
		SELECT @WantedStart = @PartLength

	IF @WantedLength < 1 OR @WantedLength IS NULL
		SELECT @WantedLength = 1

	IF @WantedLength > @PartLength - @WantedStart + 1
		SELECT @WantedLength = @PartLength - @WantedStart + 1

	SELECT	@Index = 0

	WHILE @Index < DATALENGTH(@Text) / @PartLength
		BEGIN
			INSERT	@Parts
				(
					Part
				)
			SELECT	SUBSTRING(@Text, @WantedStart + @Index * @PartLength, @WantedLength)

			SELECT	@Index = @Index + 1
		END

	RETURN
END



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