A User Defined Function in SQL simulating a VB SPLIT

Sunday, December 28, 2003

Parsing a delimited string and returning a record set seems to be a common need for database developers - Below is a SQL Server UDF that simulates a VB split function.

 

CREATE FUNCTION split(
@SplitString varchar(1000),
@Delimiter   varchar(10)
)
RETURNS @SplitTable TABLE
   (
    TableVar  varchar(20)
   )
AS
BEGIN
DECLARE @FC int
 SET @FC = -1
 WHILE (Len(@SplitString) > 0)
 BEGIN
  SET @FC = CHARINDEX(@Delimiter , @SplitString)
  IF (@FC = 0) AND (LEN(@SplitString) > 0)
  BEGIN
   INSERT INTO @SplitTable VALUES (@SplitString)
   BREAK
  END
  IF (@FC > 1)
  BEGIN
   INSERT INTO @SplitTable VALUES (LEFT(@SplitString, @FC - 1))
   SET @SplitString = RIGHT(@SplitString, (LEN(@SplitString) - @FC))
  END
  ELSE SET @SplitString = RIGHT(@SplitString, (LEN(@SplitString) - @FC))
 END

   RETURN
END

 

by Llama Lopon

by K. Scott Allen K.Scott Allen
My Pluralsight Courses
The Podcast!