/************************************************************** Name: Fn_split_text feature Description: [Split text into temporary table by delimiter] parameter introduction: [@ Text] to be split back: Table: Subcolstr: Split String Special Description: SELECT * from Fn_split_text (' xxxx,yyyy,dddd ') ************************ **************************************/if object_id('Fn_split_text')>0 Drop functionFn_split_textGOCreate functionDbo.fn_split_text (@text nvarchar(Max))returns @returntable Table(Subcolstrvarchar( $)) as begin --------------------------Define variables----------------------------------------------------------------------------- Declare @commastr varchar(Max),@splitstr varchar(Max),@splitlen int,@length int Declare @splitChar Char(1) --------------------------Assigning initial value------------------------------------------------------------------------------- Select @splitChar = ',' ----------------------------------------------------------------------------------------------------- Select @splitstr=@text, @splitlen=charindex(@splitChar,@splitstr), @length=datalength(@splitstr) while @length>0 begin -------------------------------------------------------------------------------------- if @splitlen=0 Set @splitlen=@length+1 Set @commastr =@splitstr Insert @returntable(SUBCOLSTR)Values(substring(@commastr,1,@splitlen-1)) Select @splitstr = substring(@commastr,@splitlen+1,@length), @length=datalength(@splitstr), @splitlen=charindex(@splitChar,@splitstr) -------------------------------------------------------------------------------------- End returnEndGO
Split text into temporary table (Fn_split_text) by delimiter