SQL function to split string

Here’s a handy user defined¬†function to split string into comma separated line:

/****** Object: UserDefinedFunction [dbo].[ufnSplitString] Script Date: 19/11/2015 11:18:20 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[ufnSplitString](

@String varchar(max),

@Delimiter char(1),

@Column int = 1

)

returns varchar(8000)

as

begin

declare @idx int

declare @slice varchar(max)

select @idx = 1

if len(@String)<1 or @String is null return null

declare @ColCnt int

set @ColCnt = 1

while (@idx != 0)

begin

set @idx = charindex(@Delimiter,@String)

if @idx!=0 begin

if (@ColCnt = @Column) return left(@String,@idx – 1)

set @ColCnt = @ColCnt + 1

end

set @String = right(@String,len(@String) – @idx)

if len(@String) = 0 break

end

return @String

end

Categories: SQL
Tags: