Split String by Delimiter Including Null

Posted: April 14, 2011 in SQL Server 2005

This routine will split a string into a table valued function based on the delimiter that is passed in.

-- Check if the function already exists in the database and if it does, we need to drop this before creating it again
IF EXISTS (SELECT * 
           FROM   dbo.sysobjects 
           WHERE  id = OBJECT_ID(N'dbo.FN_SplitCharByDelimiterIncNull') 
           AND    OBJECTPROPERTY(id, N'IsTableFunction') = 1)
   DROP FUNCTION dbo.FN_SplitCharByDelimiterIncNull;
GO

-- Main routine starts here
CREATE FUNCTION dbo.FN_SplitCharByDelimiterIncNull 
               (@pv_string    VARCHAR(2000),
                @pv_delimiter VARCHAR(1)) RETURNS @tbl_output TABLE (id   INT IDENTITY NOT NULL,
                                                                     item VARCHAR(2000))
BEGIN
   -- Declare variables
   DECLARE @lv_item VARCHAR(2000);

   -- Loop while the delimiter is found int he string
   WHILE CHARINDEX(@pv_delimiter, @pv_string, 0) <> 0
   BEGIN
      -- Get the item from the list and update list for next parse through
      SET @lv_item   = RTRIM(LTRIM(SUBSTRING(@pv_string, 1, CHARINDEX(@pv_delimiter, @pv_string, 0) - 1)));
      SET @pv_string = RTRIM(LTRIM(SUBSTRING(@pv_string, CHARINDEX(@pv_delimiter, @pv_string, 0) + LEN(@pv_delimiter), LEN(@pv_string))));
 
      -- If an item is not found, set it to null
      IF DATALENGTH(@lv_item) = 0
         BEGIN
            SET @lv_item = NULL;
         END;
      
      -- Add item to output table
      INSERT INTO @tbl_output SELECT @lv_item;
   END;

   -- If an item is not found, set it to null
   IF DATALENGTH(@pv_string) = 0
      BEGIN
         SET @lv_item   = NULL;
         SET @pv_string = NULL;
         INSERT INTO @tbl_output SELECT @pv_string;   
      END;
   
   IF DATALENGTH(@pv_string) > 0
      BEGIN
         INSERT INTO @tbl_output SELECT @pv_string;   
      END;
   
   -- Return output
   RETURN
END
GO

-- You can try the following test cases
SELECT * FROM dbo.FN_SplitCharByDelimiterIncNull ('1', ',');
SELECT * FROM dbo.FN_SplitCharByDelimiterIncNull ('1|2|3', '|');
SELECT * FROM dbo.FN_SplitCharByDelimiterIncNull ('1**3', '*');
SELECT * FROM dbo.FN_SplitCharByDelimiterIncNull ('1~2~~3', '~');
SELECT * FROM dbo.FN_SplitCharByDelimiterIncNull ('--3', '-');
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s