|
|
SQL Server T-SQL User-Defined Function of the Week
Volume 1 Number 29 June 3, 2003
Split Delimiter Separated Lists
Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm
To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com
Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm
Visit the T-SQL UDF of the Week at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeek.htm
Creating and parsing lists of items in a string is a frequent
request on SQL Server discussion groups. Storing multiple items
in a list runs counter to relational concepts such as
normalization. However, it's a compact format that most people
find easy to read and that many other programs use for data
exchange.
Here's an example of what I'm talking about. It's a comma-
separated list of the Three Stooges:
'Moe, Larry, Shemp, Curly, Joe, Curly-Joe'
Why 6 stooges? Shemp didn't really like getting poked in the
eye and retired. He was replaced by Curly, Joe, and then
Curly-Joe. They kept on making Stooges shorts into the 1960s.
Once you have a list in a string, it's often necessary to split
it into the individual items. That's the purpose of this
week's UDF. Here's the CREATE FUNCTION script:
/---------- Start copying below this line ----------------------\
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.udf_Txt_SplitTAB (
@sInputList varchar(8000) -- List of delimited items
, @Delimiter char(1) = ',' -- delimiter that separates items
) RETURNS @List TABLE (Item varchar(8000))
WITH SCHEMABINDING
/*
* Returns a table of strings that have been split by a delimiter.
* Similar to the Visual Basic (or VBA) SPLIT function. The
* strings are trimmed before being returned. Null items are not
* returned so if there are multiple separators between items,
* only the non-null items are returned.
* Space is not a valid delimiter.
*
* Example:
select * FROM dbo.udf_Txt_SplitTAB('abcd,123, 456, efh,,hi', ',')
*
* Test:
DECLARE @Count int, @Delim char(10), @Input varchar(128)
SELECT @Count = Count(*)
FROM dbo.udf_Txt_SplitTAB('abcd,123, 456', ',')
PRINT 'TEST 1 3 lines:' + CASE WHEN @Count=3
THEN 'Worked' ELSE 'ERROR' END
SELECT @DELIM=CHAR(10)
, @INPUT = 'Line 1' + @delim + 'line 2' + @Delim
SELECT @Count = Count(*)
FROM dbo.udf_Txt_SplitTAB(@Input, @Delim)
PRINT 'TEST 2 LF :' + CASE WHEN @Count=2
THEN 'Worked' ELSE 'ERROR' END
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published in T-SQL UDF of the Week Newsletter Vol 1 #29
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
***************************************************************/
AS BEGIN
DECLARE @Item Varchar(8000)
DECLARE @Pos int -- Current Starting Position
, @NextPos int -- position of next delimiter
, @LenInput int -- length of input
, @LenNext int -- length of next item
, @DelimLen int -- length of the delimiter
SELECT @Pos = 1
, @DelimLen = LEN(@Delimiter) -- usually 1
, @LenInput = LEN(@sInputList)
, @NextPos = CharIndex(@Delimiter, @sInputList, 1)
-- Doesn't work for space as a delimiter
IF @Delimiter = ' ' BEGIN
INSERT INTO @List
SELECT 'ERROR: Blank is not a valid delimiter'
RETURN
END
-- loop over the input, until the last delimiter.
While @Pos <= @LenInput and @NextPos > 0 BEGIN
IF @NextPos > @Pos BEGIN -- another delimiter found
SET @LenNext = @NextPos - @Pos
Set @Item = LTrim(RTrim(
substring(@sInputList
, @Pos
, @LenNext)
)
)
IF LEN(@Item) > 0
Insert Into @List Select @Item
-- ENDIF
END -- IF
-- Position over the next item
SELECT @Pos = @NextPos + @DelimLen
, @NextPos = CharIndex(@Delimiter
, @sInputList
, @Pos)
END
-- Now there might be one more item left
SET @Item = LTrim(RTrim(
SUBSTRING(@sInputList
, @Pos
, @LenInput-@Pos + 1)
)
)
IF Len(@Item) > 0 -- Put the last item in, if found
INSERT INTO @List SELECT @Item
RETURN
END
GO
GRANT SELECT ON [dbo].[udf_Txt_SplitTAB] to PUBLIC
GO
\---------- Stop copying above this line -----------------------/
Now, lets break up the Stooges:
/---------- Start copying below this line ----------------------\
SELECT Item as [Stooge]
FROM udf_Txt_SplitTAB(
'Moe, Larry, Shemp, Curly, Joe, Curly-Joe'
, ',')
\---------- Stop copying above this line -----------------------/
(Results)
Stooge
----------------------------------------
Moe
Larry
Shemp
Curly
Joe
Curly-Joe
Of course, you can use other separators if you like. However,
udf_Txt_SplitTAB can't use space as a delimiter. Spaces get
special treatment, they're stripped from the ends of the items.
This next statement illustrates:
/---------- Start copying below this line ----------------------\
SELECT '->' + Item + '<-' as [Item]
FROM udf_Txt_SplitTAB(
'1, 22 , 333, 4444 , , 55555'
, ',')
\---------- Stop copying above this line -----------------------/
(Results)
Item
----------------------------------------
->1<-
->22<-
->333<-
->4444<-
->55555<-
Next issue I'll show you how to pull of the reverse trick and
combine multiple strings into a comma separated list.
+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share? Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:
UDF@NovickSoftware.com
and they might be published in this newsletter. I try and
respond to every request that I get.
Thanks,
Andrew Novick
+--------------------------------------------------------------+
|
|
