Having trouble splitting a list to a resultset? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

Script Library

Having trouble splitting a list to a resultset?

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-29 : 20:35:58

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

Kristen
Test

22859 Posts

Posted - 2006-05-30 : 00:53:28

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-30 : 02:24:55

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

Igor2004
More clever than you

78 Posts

Posted - 2006-06-08 : 11:17:51

[code]-- 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[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-04 : 08:33:38

[code]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[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-04 : 08:33:56

[code]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[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources