T-SQL Explode

A feature I have used with much joy in PHP is the explode() function, which creates an array populated by a single parameter containing comma-separated values (CSV).  I recently had a desire to use the same approach in SQL Server programming (T-SQL) and push a set of CSV parameters into a temp table.  A slightly modified version of the function found here now allows me to do so!

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/* select * from explode(‘1,2,3’) */
CREATE FUNCTION [dbo].[explode] (
@CSVString VARCHAR(8000))
RETURNS @temptable TABLE (item VARCHAR(8000))
AS
BEGIN

• DECLARE @pos INT;
  DECLARE @slice VARCHAR(8000);
  DECLARE @Delimiter CHAR(1);
  SELECT @pos = 1;
  SELECT @Delimiter = ‘,’;
  IF LEN(@CSVString) < 1 OR @CSVString IS NULL RETURN;
  WHILE @pos!= 0
  BEGIN
  • SET @pos = CHARINDEX(@Delimiter,@CSVString);
    IF @pos != 0
    SET @slice = LEFT(@CSVString, @pos – 1);
    ELSE
    SET @slice = @CSVString;

    IF( LEN(@slice) > 0)
    INSERT INTO @temptable(item) VALUES (@slice);
    SET @CSVString = RIGHT(@CSVString, LEN(@CSVString) – @pos);
    IF LEN(@CSVString) = 0 BREAK;

  END

RETURN
END