SQL Server - Split CSV into separate row values

In this post, let us see how to split delimited string into separate rows using User defined function.

--Example for splitting CSV into separate row values

CREATE FUNCTION [dbo].[Split_CSV]
(@input VARCHAR(MAX))

RETURNS @Split TABLE(Columnslist VARCHAR(MAX))

AS

BEGIN

DECLARE     
         @l_len INT,
         @i INT=1,
         @N INT=1,
         @csv_string VARCHAR(MAX),
         @csv_strings VARCHAR(MAX)= @input + ',';   
        
        
 SET @l_len = LEN(@csv_strings)
          WHILE @i<=@l_len
          BEGIN
            IF SUBSTRING(@csv_strings,@i,1)=','
             BEGIN
               SET @csv_string = SUBSTRING(@csv_strings,@N,@i-@N)
               
               INSERT INTO @Split(Columnslist)
               SELECT @csv_string
               
               SET @N = @i+1;
             END
             SET @i = @i+1;
          END
           
RETURN ;
END
GO

--
SELECT * FROM [dbo].[Split_CSV]('SQL Server, Oracle, DB2')

See Also:

• My posts on T-SQL