TSQL Functions Inspired By ColdFusion’s Lists Functions

In my last project, there was a bit of data scrubbing on the database side (SQL Server 2008) that I decided to create a few UDF’s that function similar to ColdFusion’s Lists function. The one that varies a little bit is ListLen(), since I needed to take into account empty tokens. The ChopIf() was inspired by Perl’s chop() function. These UDFs should be SQL Server 2005-compatible.

I should say though, that some of these functions depend on each other. ListLen(), GetToken(), and ChopIf() are independent.

------------------------------------------------------------------
-- Functions similarly like ColdFusion ListSort() function,
-- except it currently only sorts strings. 
--
-- Example 1:
--    dbo.ListSort( 'dan is so mega awesome that he rules all the time', 'ASC', ' ' )
--
-- Returns: 
--    all awesome dan he is mega rules so that the time
--
-- Example 2:
--    dbo.ListSort( 'dan is so mega awesome that he rules all the time', 'DESC', ' ' )
--
-- Returns: 
--    time the that so rules mega is he dan awesome all
------------------------------------------------------------------

CREATE FUNCTION [dbo].[ListSort]
(
  @string    VARCHAR(2000),  
  @sort_type CHAR(3)       = 'ASC',
  @delimiter VARCHAR(2000) = ','
)
RETURNS VARCHAR(500)
AS

BEGIN

  DECLARE @position AS INT
  DECLARE @token AS VARCHAR (2000)
  DECLARE @counter   AS INT
  DECLARE @sortedList AS VARCHAR(500)

  DECLARE @sortTempTable TABLE ( token VARCHAR(500) )
  DECLARE @sortedTable   TABLE ( token VARCHAR(500) )  

  SELECT @string   = @string + @delimiter,
         @counter  = 1,
         @position = 0,
         @token    = ''

  WHILE ( PATINDEX( '%' + @delimiter + '%' , @string )  0 ) 
  BEGIN
    SELECT @position = PATINDEX('%' + @delimiter + '%' , @string ),
           @token    = LEFT( @string, @position - 1 ),
           @string   = STUFF( @string, 1, @position, NULL ),
           @counter  = @counter + 1

    INSERT @sortTempTable( token ) VALUES( @token )     
  END
  
  SET @sortedList = ''

  -- Let's sort the table and put it into @sortedTable
  -- Because of nature of Rank(), we can't set @sortedList in this statement.
  -- Have to separate it into another select clause.
  INSERT INTO @sortedTable
    SELECT LTRIM( token )
    FROM   @sortTempTable
    ORDER  BY CASE WHEN @sort_type = 'ASC'  THEN ( RANK() OVER ( ORDER BY LTRIM(token) ASC ) )
                   WHEN @sort_type = 'DESC' THEN ( RANK() OVER ( ORDER BY LTRIM(token) DESC ) )
              END  

  SELECT @sortedList = @sortedList + token + @delimiter
  FROM   @sortedTable

  RETURN dbo.ChopIf( @sortedList, @delimiter )

END
GO

------------------------------------------------------------------
-- Functions sort of like ColdFusion's ListLen() method, but it
-- takes into account empty tokens. 
--
-- Example 1:
--    dbo.ListLen( 'Dan is cool', ' ' )
--
-- Returns: 
--    3
-- 
-- Example 2:
--    dbo.ListLen( 'dan,,very,,,,awesome,', ',' )
--
-- Returns: 
--    8
------------------------------------------------------------------

CREATE FUNCTION [dbo].[ListLen]
(
  @string VARCHAR(2000),
  @delimiter VARCHAR(2000) = ','
)
RETURNS INT
AS
BEGIN

  DECLARE @loopCount INT, 
          @tokenCount INT

  SELECT @loopCount = 0, 
         @tokenCount = 0

  -- If it's an empty string, the list length is 0
  IF DATALENGTH( @string ) = 0
    BEGIN
      SET @tokenCount = 0
    END
  ELSE
    BEGIN
      -- Count tokens, including empty ones like dan,,very,,,,awesome,
      SET @tokenCount = @tokenCount + 1
      WHILE ( @loopCount < DATALENGTH( @string ) )
      BEGIN
        IF SUBSTRING( @string, @loopCount, DATALENGTH( @delimiter ) ) = @delimiter
          BEGIN
            SET @tokenCount = @tokenCount + 1
          END
        SET @loopCount = @loopCount + 1
      END
    END

  -- Handle extra count from space being delimiter
  IF @delimiter = ' '
    SET @tokenCount = @tokenCount - 1

  -- If there's no token to the right of the last delimiter, then count that
  -- as an empty token.
  IF ( RIGHT( @string, 1 ) = @delimiter ) 
  BEGIN
    SET @tokenCount = @tokenCount + 1
  END

  RETURN @tokenCount

END
GO

——————————————————————
— Functions like ColdFusion's ListLast()
— Gets token value that's been separated by a delimiter.
—
— Example:
— dbo.ListLast( 'Dan is cool', ' ' )
—
— Returns:
— cool
——————————————————————

CREATE FUNCTION [dbo].[ListLast]
(
@string VARCHAR(2000),
@delimiter VARCHAR(2000) = ','
)
RETURNS VARCHAR(2000)
AS
BEGIN

RETURN dbo.ListGetAt( @string, dbo.ListLen( @string, @delimiter ) , @delimiter )

END
GO

——————————————————————
— Wrapper for GetToken() Function
— Gets token value that's been separated by a delimiter.
—
— Example:
— dbo.ListGetAt( 'Dan is cool', 2, ' ' )
—
— Returns:
— is
——————————————————————

CREATE FUNCTION [dbo].[ListGetAt]
(
@string VARCHAR(2000),
@token INT,
@delimiter VARCHAR(2000)
)
RETURNS VARCHAR(2000)
AS
BEGIN
RETURN dbo.GetToken( @string, @token, @delimiter )
END
GO

——————————————————————
— Returns the first item in a tokenized list.
—
— Example:
— dbo.ListFirst( 'Dan is cool', ' ' )
—
— Returns:
— Dan
——————————————————————

CREATE FUNCTION [dbo].[ListFirst]
(
@string VARCHAR(2000),
@delimiter VARCHAR(2000) = ','
)
RETURNS VARCHAR(2000)
AS
BEGIN

RETURN dbo.ListGetAt( @string, 1, @delimiter )

END
GO

——————————————————————
— Functions similarly like ColdFusion GetToken() Function.
— Gets token value that's been separated by a delimiter.
—
— Example:
— dbo.GetToken( 'Dan is cool', 2, ' ' )
—
— Returns:
— is
——————————————————————

CREATE FUNCTION [dbo].[GetToken]
(
@string VARCHAR(2000),
@tokenPosition INT,
@delimiter VARCHAR(2000)
)
RETURNS VARCHAR(2000)
AS
BEGIN

DECLARE @position AS INT
DECLARE @token AS VARCHAR (2000)
DECLARE @counter AS INT

SELECT @string = @string + @delimiter,
@counter = 1,
@position = 0,
@token = ''

WHILE ( PATINDEX('%' + @delimiter + '%' , @string ) 0) AND ( @tokenPosition + 1 @counter )
BEGIN
SELECT @position = PATINDEX(‘%’ + @delimiter + ‘%’ , @string),
@token = LEFT(@string, @position-1),
@string = STUFF(@string, 1, @position, null),
@counter = @counter + 1
END

RETURN @token
END

------------------------------------------------------------------
-- Chops the last character if it's @chopped
--
-- Example:
--    dbo.ChopIf( 'Dan is cool!', '!' )
--
-- Returns: 
--    Dan is cool
------------------------------------------------------------------

CREATE FUNCTION [dbo].[ChopIf]
(
  @string VARCHAR(2000),
  @chopped VARCHAR(2000)
)
RETURNS VARCHAR(2000)
AS
BEGIN

  IF ( RIGHT( @string, DATALENGTH(@chopped) ) = @chopped )
  BEGIN
    SET @string = LEFT( @string, DATALENGTH( @string ) - DATALENGTH( @chopped ) ) 
  END 

  RETURN @string
  
END
GO