April 2010 Tip of the Month

SQL Server Development:  Take apart delimited strings with ease with a function that is commonly found in Visual Basic.  Pass a delimited string into the fnSplit function and it will output the string as a table that can be queried.

The function is called by this statement:

SELECT * FROM dbo.fnSplit(‘SQLRx,Solves,Performance,Problems’,’,’)

It will output a table…give it a try!

CREATE FUNCTION [dbo].[fnSplit] (@String VARCHAR(8000), @Delimiter CHAR(1))

RETURNS @temptable TABLE (items VARCHAR(8000))

AS

BEGIN

DECLARE @idx INT

DECLARE @slice VARCHAR(8000)

SELECT @idx = 1

IF LEN(@String)<1 OR @String IS NULL  RETURN

WHILE @idx!= 0

BEGIN

SET @idx = CHARINDEX(@Delimiter,@String)

IF @idx!=0

SET @slice = LEFT(@String,@idx – 1)

ELSE

SET @slice = @String

IF(LEN(@slice)>0)

INSERT INTO @temptable(items) VALUES(@slice)

SET @String = RIGHT(@String,LEN(@String) – @idx)

IF LEN(@String) = 0 BREAK

END

RETURN

END

GO