Split a string separated by a deliminator into a table

 

--Creates an 'InLine' Table Valued Function (TVF)
-- Author: krishang
-- Date: 28 Feb -2011 
-- Remark: this function is similar to the uIntIDTotable however takes a string and returns a table with the string value
-- sperated by the delimiter. also includes an index which for the elements.
create FUNCTION [dbo].[uSplit] 
   (  @Delimiter varchar(5), 
      @List      varchar(max)
   ) 
   RETURNS @TableOfValues table 
      (  RowID   smallint IDENTITY(1,1), 
         [Value] varchar(max) 
      ) 
AS 
   BEGIN
    
      DECLARE @LenString int 
 
      WHILE len( @List ) > 0 
         BEGIN 
         
            SELECT @LenString = 
               (CASE charindex( @Delimiter, @List ) 
                   WHEN 0 THEN len( @List ) 
                   ELSE ( charindex( @Delimiter, @List ) -1 )
                END
               ) 
                                
            INSERT INTO @TableOfValues 
               SELECT substring( @List, 1, @LenString )
                
            SELECT @List = 
               (CASE ( len( @List ) - @LenString ) 
                   WHEN 0 THEN '' 
                   ELSE right( @List, len( @List ) - @LenString - 1 ) 
                END
               ) 
         END
          
      RETURN 
      
   END
GO