Friday, 15 June 2007

Split function

Another script that I'm forever tracking down.


CREATE FUNCTION [dbo].[fn_Split] (
@arr AS VARCHAR(MAX)
, @sep AS CHAR(1)
)
RETURNS TABLE
AS
RETURN


SELECT n - Len(REPLACE(LEFT(@arr,n),@sep,'')) + 1 AS pos
,CAST(Substring(@arr,n,Charindex(@sep,@arr + @sep,n) - n) AS INT) AS Element
FROM (SELECT @arr AS arr) AS a
JOIN dbo.nums
ON n <= Len(@arr)
AND Substring(@sep + @arr,n,1) = @sep

2 comments:

Najm said...

Phills,
It looks like a good function
it only compiles and fails when one tries to create the function. I suppose you did not mention anything about nums table that is required for this function to work.

-Jim

Phill Carter said...

Yes it does need a nums table. The uses of a nums table are many and varied and I keep one on all of my servers.

Here's a script to generate the table quickly and easily,
http://philcart.blogspot.com/2007/05/script-to-generate-nums-table.html