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
Categories