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

Quick and Easy T-SQL Split Statement

T-SQL

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

Advertisements
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: