/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Monday, June 2, 2014

Useful sql server user defined functions split function

User defined function :-

Split function

Creating user defined function for splitting string with any delimiter like comma(,) colon(:) or any special characters separated.

CREATE FUNCTION [udf_Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
        declare @idx int
        declare @slice varchar(8000)
 

        select @idx = 1
                if len(@String)<1 is="" nbsp="" null="" or="" p="" return="" tring=""> 

        while @idx!= 0
        begin
                set @idx = charindex(@Delimiter,@String)
                if @idx!=0
                        set @slice = left(@String,@idx - 1)
                else
                        set @slice = @String
             

                if(len(@slice)>0)
                        insert into @temptable(Items) values(@slice)


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


How to use ?

SELECT items FROM dbo.udf_Split('Yogesh,Chandra,Upreti', ',')


No comments:

Post a Comment

Recent Posts

My Blog List