Friday, June 4, 2010

Best Split UDF function with delimeter in T-SQL to return result as a table

This is what the function I am using since many years to get the table of values in the given ID's which are separated by some delimiter.

Simple to understand and see the example in the comments in below function.
From the UI there are sometimes need of sending the data or ID's in the string format separated by a delimiter. So, in database, we need to grab them and apply some operations like with the ID's we need to get the records from a table. In this case, we need to use this UDF function. Everyone knows that inner joins are far better than IN clause or something else. So, what the approach I have taken here is, I will get the delimited string and delimiter and pass them to UDF function. It returns me a table which has the rows consists of the ID's. By using this table, I will apply whatever operation I want and get the result.
UDF Function:
GO
/****** Object:  UserDefinedFunction [dbo].[fnStringSplitter]    Script Date: 04/04/2006 11:44:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*******************************************************************************************************/
--            Author: Praveen Battula
--            Date: 04/04/2006                     /*******************************************************************************************************/
CREATE Function [dbo].[fnStringSplitter]
(   
    @IDs Varchar(max) --A big string which may have delimeter in it or not
    ,@Delimiter Varchar(1) -- Delimeter to use for splitting up the given string
)
/*********************** RETURN *********************/
--Returns the table with specific values in a temporary table. Useful especially if you have any IDs in the
--given string and want to get them as a table row values.
-- Example:
--@IDs = 1,2,3,4,5,6,7,8,9,10
--@Delimeter = ','
--Returns @Tbl_IDS, which is having 10 rows with above IDS in each row by splitting up with given delimeter [in this example ',']
/****************************************************/
Returns @Tbl_IDs Table (ID Varchar(500)) As
Begin
    --Remove the leading delimiter if any
    while (substring(@IDs,1,1) =@Delimiter)
        set @IDs = substring(@IDs, 2,len(@IDs)-1)

    -- Append comma
    Set @IDs = @IDs + @Delimiter

    -- Indexes to keep the position of searching
    Declare @Pos1 Int
    Declare @pos2 Int
    Declare @RowNum Int

    -- Start from first character
    Set @Pos1=1
    Set @Pos2=1
    While @Pos1 < len(@IDs)
    Begin
        Set @Pos1 = CharIndex(@Delimiter,@IDs,@Pos1)
        Insert @Tbl_IDs Values (Substring(@IDs,@Pos2,@Pos1-@Pos2))
        -- Go to next non comma character
        Set @Pos2=@Pos1+1
        -- Search from the next charcater
        Set @Pos1 = @Pos1+1
    End
    Return
End
This is not a very complex logic to understand. And below is the usage of the function.
DECLARE @EmployeeIDs VARCHAR(MAX);
SET @EmployeeIDs = '1,2,3,4,5,6,7,8,9,10';
SELECT * FROM [Employee] as e INNER JOIN  dbo.fnStringSplitter(@EmployeeIDs, ',') as eIDs ON e.EmployeeID = eIDs.ID;

So, here I have passed a string which consists of IDs to the UDF function along with delimiter as second parameter and it returns me the table with IDs as records. Now, I am simply applying the inner join on the primary table and getting what actually I need. Very simple concept and implementaion.

Hope you like this function and please let me know, if you have any ways to improve this or issues with it.

3 comments:

  1. I made some changes to your code ;)
    (a problem with @Pos 2 in while)

    Bye, Vince

    CREATE Function [dbo].[fnStringSplitter]
    (
    @IDs Varchar(max), --A big string which may have delimeter in it or not
    @Delimiter Varchar(1) -- Delimeter to use for splitting up the given string
    )
    /*********************** RETURN *********************/
    --Returns the table with specific values in a temporary table. Useful especially if you have any IDs in the
    --given string and want to get them as a table row values.
    -- Example:
    --@IDs = 1,2,3,4,5,6,7,8,9,10
    --@Delimeter = ','
    --Returns @Tbl_IDS, which is having 10 rows with above IDS in each row by splitting up with given delimeter [in this example ',']
    /****************************************************/
    Returns @Tbl_IDs
    Table (Id int identity(1,1),
    Data Varchar(500)) As
    Begin
    --Remove the leading delimiter if any
    while (substring(@IDs,1,1) =@Delimiter)
    set @IDs = substring(@IDs, 2,len(@IDs)-1)

    -- Append comma
    Set @IDs = @IDs + @Delimiter

    -- Indexes to keep the position of searching
    Declare @Pos1 Int
    Declare @pos2 Int
    Declare @RowNum Int

    -- Start from first character
    Set @Pos1=1
    Set @Pos2=1
    While @Pos1>0
    Begin
    Set @Pos1 = CharIndex(@Delimiter,@IDs,@Pos1)
    Insert @Tbl_IDs Values (Substring(@IDs,@Pos2,@Pos1-@Pos2))
    -- Go to next non comma character
    Set @Pos2=@Pos1+1
    -- Search from the next charcater
    Set @Pos1 = @Pos1+1
    End
    Return
    End

    ReplyDelete
  2. just a little enhancement to remove leading and trailing delimiters:

    set @IDs = REPLACE(RTRIM(LTRIM(REPLACE(@IDs,@Delimiter,' '))),' ',@Delimiter)

    Vince

    ReplyDelete
  3. Modified your code for brevity. Leading and trailing delimiters may be significant, so you should filter them at the caller.

    CREATE FUNCTION [dbo].fnSplitString (@InputString varchar(max), @Delimiter varchar(1) )
    RETURNS @Tbl TABLE (Item varchar(500))
    AS BEGIN
    SET @InputString = @InputString + @Delimiter
    DECLARE @Pos1 int=1, @Pos2 int=1, @len int = LEN(@InputString)
    WHILE @Pos1 < @len BEGIN
    SET @Pos1 = CHARINDEX(@Delimiter,@InputString,@Pos1)
    INSERT @Tbl VALUES (SUBSTRING(@InputString,@Pos2,@Pos1-@Pos2))
    SET @Pos2 = @Pos1+1 -- Go to next non comma character
    SET @Pos1 = @Pos1+1 -- Search from the next charcater
    END
    RETURN
    END

    ReplyDelete