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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
------------------------------------------------------------------
-- 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
------------------------------------------------------------------
-- 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
------------------------------------------------------------------
-- 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
------------------------------------------------------------------
-- 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
------------------------------------------------------------------
-- 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
------------------------------------------------------------------
-- 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
------------------------------------------------------------------
-- 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

Leave a Reply