Last Friday I was discussing with Jamie one of the SQL function I was working on. At the end of it Jamie gave me some encouragement to blog it (“If I don’t see a blog on it by Monday, I will be having a word with you!” Jamie).
With that support here I am, finally writing my first blog.
My current assignment is with the world’s leading, specialist insurance market. The situation I came across was like this, I was receiving a comma delimited string in one of my stored procedures and it was meant to be used in a where clause.
e.q.
SELECT … FROM … WHERE ITEMID IN (Comma delimited string)
The first solution that sprang to my mind was to construct a dynamic SQL in stored procedure utilising the delimited string and executing it at the end of it. This would have taken away all the obvious benefits a stored procedure offers plus would have opened the doors to potential abuse of this stored proc. Another solution would have been to use OPENXML but I didn't have a choice of getting input string in XML format.
After some further thinking and search the better solution seemed to be a SQL function accepting comma delimited string and returning a resultset which then could be used in a WHERE clause or a JOIN.
The first function developed worked to the expectations and I was delighted with it. But after a few minutes, questions started popping up in my head about the inflexibility of this function. First it was assuming the string would always be delimited with a comma and hence the other implication was a delimiter would always be a single character. Secondly the function was returning a single column table of type int. So the input and output format were fixed (arrrrrrggh!!!).
So before letting those limitations haunt me in the middle of the night, I decided to make this function a bit more generic and what follows is the result of those efforts:
-- ===========================================================
-- Author: Manoj Oswal (Conchango)
-- Create date: 26 Jan 2007
-- Description: Generic Split function
-- Accepts List and separator as string
-- Returns single variant column TABLE of items
-- ===========================================================
CREATE FUNCTION [dbo].[GenericStringSplit]
(
@List VARCHAR(8000),
@Separator VARCHAR(10)
)
RETURNS
@ItemsList TABLE
(
Item SQL_VARIANT
)
AS
BEGIN
DECLARE @CurrItem SQL_VARIANT, @Pos INT
SET @List = LTRIM(RTRIM(@List))+ @Separator
SET @Pos = CHARINDEX(@Separator, @List, 1)
IF REPLACE(@List, @Separator, '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @CurrItem = LTRIM(RTRIM(LEFT(@List, (@Pos -1 ))))
IF @CurrItem <> ''
BEGIN
INSERT INTO @ItemsList (Item)
VALUES (@CurrItem)
END
SET @List = RIGHT(@List,(LEN(@List) - (@Pos + LEN(@Separator)-1)))
SET @Pos = CHARINDEX(@Separator, @List, 1)
END
END
RETURN
END
The above function now accepts a delimited string and a separator up to 10 char and returns a single column table of sql_variant. The consuming SQL can then cast the return of this function with the desired type.
Usage:
SELECT CAST(Item AS INT) FROM [GenericStringSplit]('1;-2;-3;-4;-5',';-')
SELECT CAST(Item AS DATETIME) FROM [GenericStringSplit]('12 feb 2005~~~10 Jan 2006~~~15 Jan 2006','~~~')
One of the limitations of this function is the input list size is limited to 8000. VARCHAR(MAX) is the possible solution for this problem but that also means some more changes in the function namely changing @CurrItem type to something like VARCHAR(100). This is due to the incompatibility of VARCHAR(MAX) and SQL_VARIANT.
Interestingly enough the same day I had to write something to do the opposite of the above mentioned function. I had to write something that returned a comma separated list of items from a table. The solution I used is as follows:
DECLARE @List VARCHAR(MAX)
SET @List = ''
SELECT @List = @LIST + ',' + COLNAME FROM TABLE
IF @List <>''
BEGIN
SET @List = STUFF(@List,1,1,'')
END
SELECT @List
Please send any feedback/suggestions you have, to further improve this work.