Friday, 22 February 2013

Split string Function in SQL Server



Create FUNCTION [dbo].[Split]
(
       @String varchar(MAX),
       @Delimiter char(1)
)      
RETURNS @TempTable TABLE (Items varchar(MAX))      
AS      
BEGIN      
    DECLARE @Indx int
    DECLARE @Slice varchar(MAX)          
   
    IF len(@String)<1 or @String is null  RETURN     
    
    SET @Indx = 1 
   
    WHILE @Indx != 0
    BEGIN      
        SET @Indx = charindex(@Delimiter, @String)
       
        IF @Indx != 0
            SET @Slice = left(@String, @Indx - 1)      
        ELSE      
            SET @Slice = @String      
         
        IF(len(@Slice)>0) 
            INSERT INTO @TempTable(Items) VALUES (@Slice)      
 
        SET @String = right(@String,len(@String) - @Indx)      
        IF len(@String) = 0 BREAK      
    END
    RETURN      
END

Ex:
Select * from Split('A,B,C,D', ',')

Output:
Items
A
B
C
D

No comments:

Post a Comment