Convert delimited string to column row data in Microsoft SQL Server

Script for Convert Delimited String to Column in Microsoft SQL Server

Create a function for convert delimited string to Column row data:

CREATE FUNCTION cmt_data_user.Split(@String varchar(max), @Delimiter char(1))
returns @temptable TABLE (items varchar(max))
as
begin
declare @idx int
declare @slice varchar(max)

select @idx = 1
if len(@String)0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

For Output:

select * from dbo.split('aaa,bbb,ccc',',');

Result:
-------
aaa
bbb
ccc

Advertisements

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.