Split string in Sql Server


Today I found very good solution, to split the string in Sql Server:
//Sql Function

CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
GO

//How to uses
DECLARE @DelimitedString NVARCHAR(128)
SET @DelimitedString = 'Duckman,Cornfed,Ajax,Charles,Mambo'
SELECT * FROM Split(@DelimitedString, ',')

http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql/

Advertisements
Tagged with:
Posted in SQL Server

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

Top Rated

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

Blog Stats
  • 29,596 hits
%d bloggers like this: