Over a million developers have joined DZone.

SQL Fn_split

·
CREATE function dbo.fn_Split(
 @String nvarchar (4000),
 @Delimiter nvarchar (10)
 )
returns @ValueTable table ([Value] nvarchar(4000))
begin
 declare @NextString nvarchar(4000)
 declare @Pos int
 declare @NextPos int
 declare @CommaCheck nvarchar(1)
 
 if @String <> '' 
 begin
	 --Initialize
	 set @NextString = ''
	 set @CommaCheck = right(@String,1) 
 
	 --Check for trailing Comma, if not exists, INSERT
	 --if (@CommaCheck <> @Delimiter )
	 set @String = @String + @Delimiter
 
	 --Get position of first Comma
	 set @Pos = charindex(@Delimiter,@String)
	 set @NextPos = 1
 
	 --Loop while there is still a comma in the String of levels
	 while (@pos <>  0)  
	 begin
	 	set @NextString = substring(@String,1,@Pos - 1)
 
	 	insert into @ValueTable ( [Value]) Values (@NextString)
 
	  	set @String = substring(@String,@pos +1,len(@String))
  
	  	set @NextPos = @Pos
	  	set @pos  = charindex(@Delimiter,@String)
	 end
 end
 
 return
end
Topics:

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}