Home > SQL Server > Quick and Easy T-SQL Split Statement

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))
table as return
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 
        indices where S >0

Categories: SQL Server Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: