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
