SQL Split String function

Found this useful:

 

create function dbo.SplitString 

(

    @str nvarchar(max), 

    @separator char(1)

)

returns table

AS

return (

with tokens(p, a, b) AS (

    select 

        cast(1 as bigint), 

        cast(1 as bigint), 

        charindex(@separator, @str)

    union all

    select

        p + 1, 

        b + 1, 

        charindex(@separator, @str, b + 1)

    from tokens

    where b > 0

)

select

    p-1 ItemIndex,

    substring(

        @str, 

        a, 

        case when b > 0 then b-a ELSE LEN(@str) end) 

    AS Item

from tokens

);

 

GO

 

 

–Call it like this

— select * from dbo.splitString(‘hello john smith’, ‘ ‘)

— from http://stackoverflow.com/questions/2647/split-string-in-sql

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