Quick and Easy T-SQL Split Statement

Here’s a quick and easy Split function to split a comma separated list (for example, although it could be separated with any character) into a table that can be easily joined in the calling statement.

The only bad news here is that you will need SQL 2005 or above for this to work (if my memory serves me correctly) – but then if you’re still running SQL 2000 one has to beg the question, ‘Why?’. 

Anyway – over to the code:

create function Split ( @StringToSplit varchar(2048), @Separator varchar(128))
returns 
table as return
with 
indices as
(
    select 0 S, 1 E
    union all
    select E, charindex(@Separator, @StringToSplit, E) + len(@Separator)
    from indices
    where E > S
)
    select substring(@StringToSplit,S,case 
        when E > len(@Separator) then e-s-len(@Separator) 
        else len(@StringToSplit) - s + 1 end) String, S StartIndex 
    from
        indices where S >0