SQL Server Split function I know there are some answers about a user function to split chars and I have one already running (found one years ago in the net and modified it to my own needs).

Since I use this function very often, I'd like to ask the best SQL professionals (which are probably here) to review this function for performance. Is the function ok or is there a faster or better way to do this now? Minimum requirements are SQL Server 2005 to work, but if there are better ways from 2008 I can roll out two versions (most usage of the DB is 2008, only a few 2005 left).

CREATE FUNCTION [dbo].[fn_Split](@text nvarchar(4000), @delimiter char(1) = ',')

RETURNS @Strings TABLE
(
position int IDENTITY PRIMARY KEY,
value nvarchar(4000)
)

AS
BEGIN

DECLARE @index int
SET @index = -1

SET @text = RTRIM(LTRIM(@text))

WHILE (LEN(@text) &gt; 0)
BEGIN
  SET @index = CHARINDEX(@delimiter , @text)
  IF (@index = 0) AND (LEN(@text) &gt; 0)
  BEGIN
    INSERT INTO @Strings VALUES (@text)
    BREAK
    END
    IF (@index &gt; 1)
    BEGIN
    INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
    SET @text = RIGHT(@text, (LEN(@text) - @index))
  END
  ELSE
    SET @text = RIGHT(@text, (LEN(@text) - @index))
  END
  RETURN
END
try this:

Create function dbo.SplitString(@inputStr varchar(1000),@del varchar(5))
RETURNS @table TABLE(col varchar(100))
As
BEGIN

DECLARE @t table(col1 varchar(100))
INSERT INTO @t
select @inputStr

if CHARINDEX(@del,@inputStr,1) &gt; 0
BEGIN
    ;WITH CTE1 as (
    select ltrim(rtrim(LEFT(col1,CHARINDEX(@del,col1,1)-1))) as col,RIGHT(col1,LEN(col1)-CHARINDEX(@del,col1,1)) as rem from @t
    union all
    select ltrim(rtrim(LEFT(rem,CHARINDEX(@del,rem,1)-1))) as col,RIGHT(rem,LEN(rem)-CHARINDEX(@del,rem,1))
    from CTE1 c
    where CHARINDEX(@del,rem,1)&gt;0
    )

        INSERT INTO @table 
        select col from CTE1
        union all
        select rem from CTE1 where CHARINDEX(@del,rem,1)=0
    END
ELSE
BEGIN
    INSERT INTO @table 
    select col1 from @t
END

RETURN

END