Jerry Nixon on Windows: No Split in SQL? Well, here's the method you need!

Tuesday, April 22, 2008

No Split in SQL? Well, here's the method you need!

Splitting a string is an important work-around for not having array-type arguments to stored procedures and functions. Passing in a list of values for, say, a cross reference table value can save a lot of needless database calls by combining them in one and treating them as a single known quantity. Here's the code:

IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[fn_split]'))
DROP FUNCTION [dbo].[fn_split]
GO

CREATE FUNCTION fn_Split(@text varchar(max), @delimiter char(1) = ',')
RETURNS @TABLE TABLE
(
[position] int IDENTITY PRIMARY KEY,
[value] varchar(max)
)
AS
BEGIN

DECLARE @index int
SET @index = -1

WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @TABLE VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @TABLE VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END
GO

select
position
,convert(varchar(10), value) as value
from
dbo.fn_Split('dog,cat,pig,cow,rat', default)
order by
value desc

/*

output

position value
----------- ----------
5 rat
3 pig
1 dog
4 cow
2 cat


*/