TSQL Functions Inspired By ColdFusion’s Lists Functions

Posted by Dan | Posted in ColdFusion, Databases, SQL Server | Posted on 04-27-2010

0

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

Generate Junk Files

Posted by Dan | Posted in Python | Posted on 04-24-2010

0

The other day I was testing benchmarks for a delete script. I needed to create files with various file sizes. More specific, 1,000,000 files with 5K per file. A while ago I found this great snippet on StackOverflow to generate a junk random string:

junk =  (("%%0%dX" % (junk_len * 2)) % random.getrandbits(junk_len * 8)).decode("hex")

I’ve wrapped that around to make a utility function and snippet:

import os, random, sys
 
# This tool takes 3 parameters
#
#   testing <directory to put files in> <how many files> <size of each file in bytes>
#
# Example:
#
#   testing dan 100 500
 
def createLocalDirectory( directoryName ):
  if not os.path.exists( directoryName ):
    os.makedirs( directoryName )
 
folderName      = sys.argv[1]
how_many_files  = int(sys.argv[2])
junk_len        = int(sys.argv[3])
 
 
createLocalDirectory( folderName )
 
for i in range( 0, how_many_files ):  
  junk =  (("%%0%dX" % (junk_len * 2)) % random.getrandbits(junk_len * 8)).decode("hex")
  path = folderName + "/" + str(i) + ".txt"
  f = open( path, 'w' )
  f.write( junk )
  print f
  f.close()

Search In One File from Keywords in Another File

Posted by Dan | Posted in Python, Web Development | Posted on 04-24-2010

0

I needed to see if this list of email addresses were found in a logs file. So I had one file with a list of email addresses. Another file is a list emails sent. I needed to make sure that the emails were sent. Here’s a quick Python script I put together that does this:

import re
import sys
 
def searchInLogFile( FILE, query ):  
  FILE.seek( 0, 0 )  
  for line in LogFile:
    logLine = line.replace("\n","").replace("\r","").rstrip().lstrip()
    if re.search( query, logLine, re.IGNORECASE | re.MULTILINE ):
      return True
 
# This file has a list (\r\n delimited) email addresses.
EmailListFile = open( "email-list-internal.txt", "r")
 
# This is the log file which we'll use to see if email addresses are in here.
LogFile = open( "POST20100201.log", "r" )
 
EmailFound = []
EmailNotFound = []
breakTime = 0
# 0 = does the whole list
EmailsToSearchFor = 0
 
for emailLine in EmailListFile:
  email = emailLine.replace("\n","").replace("\r","").rstrip().lstrip()
 
  if ( searchInLogFile( LogFile, email ) ):
    print email, "was found"
    EmailFound.append( email )
 
  else:
    print email, "not found"
    EmailNotFound.append( email )  
 
 
  if ( EmailsToSearchFor != 0 ):
    breakTime += 1
    if ( breakTime == EmailsToSearchFor ):
      break;
 
LogFile.close()
EmailListFile.close()
 
# Log results to a file.
OutputFile = open( "output.log", "w" )
 
divider = "\n\n======== Found ========================================"
print divider
OutputFile.write( divider )
 
for i in EmailFound:
  print i
  OutputFile.write( "\n" + i )
 
divider = "\n\n======== Not Found ===================================="  
print divider
OutputFile.write( divider )
 
for i in EmailNotFound:
  print i
  OutputFile.write( "\n" + i )
 
OutputFile.close()

Pretty straightforward. The script also writes a file called “output.log” which has a list of emails that were found (marked under “found”) and not found (marked under “not found”).

RedGate SQL Search

Posted by Dan | Posted in Databases, SQL Server | Posted on 04-24-2010

0

I love this tool. It’s a free add-on if you own SQL Prompt, RedGate’s version of intellisense, which is better, in my eyes, than Microsoft’s. It uses SQL Prompt’s FTS collection to search for any piece of string in any DB object. It’s crazy fast. What I like the most though, is the ability to right click on a result, and move the focus to appropriate DB object in the explorer panel. Hit CTRL+ALT+D to trigger the search screen and start typing away.

Connection Problem with SQL Server 2008

Posted by Dan | Posted in Databases, SQL Server | Posted on 04-24-2010

0

I can’t say how many various problems I’ve had connecting to SQL Server. I’ve lost count at this point. This was the last one I had, which I’ve encountered a number of times and always successful to fix it.

Cannot connect to x.x.x.x.

Additional Information:
A network-related or instance-specific error occurred while establishing a connection to SQL Server….

Now usually when I encounter this error, I do something suggested here at Pinal Dave’s site. This time nothing mentioned there worked. What did work, was me explicitly selecting the Network protocol, and not leaving it as the :

Still wondering why I have to do this, as I’ve never had to explicitly select the protocol before, and nothing on the server has changed in years. Will continue to investigate.

Fast Delete

Posted by Dan | Posted in Automation / Scripting, Systems | Posted on 04-09-2010

0

I needed to delete 30,000 files that took about 60 GB. I know traditionally it takes forever to delete it in Windows. Even using the “del” command in the console, it’s always been slow for me. Enter Robocopy. I never gave it a chance until today. The way it can delete is by syncing directory against an empty one which was just what I needed. It was able to do it in about 50 seconds. Awesome.

You’ll have to download Robocopy as part of the Windows Server 2003 Resource Kit Tools.

What I used to delete this big directory:

robocopy /MIR c:\empty-directory c:\my-files-to-delete

Compare Table Data

Posted by Dan | Posted in Databases, SQL Server | Posted on 04-04-2010

0

Currently I use RedGate’s SQL Data Compare to see differences in data across databases. It’s a great product. One thing I love about RedGate is the usability of their products. All of them are intuitive and easy to use. What if you don’t have the cash to do this though?

You could use the EXCEPT (introduced in SQL Server 2005) clause to do your comparison. For example:

  SELECT  *  
  FROM    #table2 -- returns 30 records
 
  EXCEPT
 
  SELECT  *  
  FROM    #table1 -- returns 10 records.

You get a result set of 20 records if those 10 records in the table1 statement are in table2. You can think of it as subtracting records from table2 that are in table1. You will get the end result of 20 records.

The following statement builds on that taking the end result and putting it back into table1. This is useful if you want to sync an outdated table (in this case table1) with another table that has additional records (table2).

INSERT INTO #table1
  SELECT  *  
  FROM    #table2 -- latest data table (we'll be copying from here)
 
  EXCEPT
 
  SELECT  *
  FROM    #table1 -- outdated table (has old data)

Other tools to compare data across tables: CompareData may let you do what you want, and its table compare is free unlimited, I believe, for the evaluation version. The great thing about this tool is also that it will check results of two SQL statements / stored procs. Useful when unit testing.

Also you can use TableDiff.exe in your “C:\Program Files\Microsoft SQL Server\90\COM” directory. Learn more about it at the SQL team website.

Quick Simple Way to Show/Hide with a Checkbox using jQuery

Posted by Dan | Posted in JavaScript | Posted on 04-03-2010

0

Here’s a quick and simple way to do this. I often have to show and hide set of panels based on whether something is checked or not. jQuery code is pretty straightforward and it comes in handy.

$( function() {    
 
  function toggleCheckbox( trigger, hidethese )
  {
    if ( $( trigger ).attr("checked") )
    {
      $( hidethese ).hide();
    }
    else
    {
      $( hidethese ).show();
    }
  }
 
  // jQuery object that has your checkbox
  var triggerCheckbox = $( "#showPreonDescription" );
 
  // jQuery object for collection of elements you want to hide
  // For this example, I could've also just done #panel by itself
  // to hide everything
  var panelsToHide = $("#subpanel,#maincontent,#nav");  
 
  // Call the function on click.
  triggerCheckbox.click(
    function ()
    {
      toggleCheckbox( triggerCheckbox, panelsToHide );
    }
  );
});

I have the demo if you want the source as well.

IsNumericList() Function

Posted by Dan | Posted in ColdFusion | Posted on 04-03-2010

0

Whipped up this UDF while working on a project. It checks to see if the list is valid to use in a TSQL WHERE IN Clause. I wanted to check for a list of valid positive integers (including 0), ignoring spaces.

<cffunction name="isNumericList" returntype="boolean" hint="Check for either single positive number or a set of positive numbers. Spaces ignored." >
 
  <!--- Useful when inserting into an "IN" TSQL list in the WHERE clause.  --->
 
  <cfargument name="list" required="true">
 
  <cfargument name="delimiter" required="false" default=",">   
 
  <cfset var isNumericList = false />
 
  <cfif REFind( "^(\d+)$|^(([\d\s]+#Arguments.delimiter#)+\s*\d+)$", Trim(Arguments.list) ) >
 
    <cfreturn true >
 
  </cfif>
 
  <cfreturn isNumericList >  
 
</cffunction>

Now let’s test it!

<!--- Dummy Data --->
<cfset dataList = ArrayNew(1) />
<cfset dataList[1] = "456c" />
<cfset dataList[2] = "456" />
<cfset dataList[3] = "45c," />
<cfset dataList[4] = "5,,," />
<cfset dataList[5] = "565,651,34,643232,45" />
<cfset dataList[6] = "454,c,45,5454,32" />
<cfset dataList[7] = "121.45,43,565,1,1,2" />
<cfset dataList[8] = "43,54,65,1," />
<cfset dataList[9] = "67,54,73,436," />
<cfset dataList[10] = ",6565,656,77,32,3" />
<cfset dataList[11] = "" />
<cfset dataList[12] = ",43656" />
<cfset dataList[13] = "4365,  55,31,24,   5,   5  ,1,      34" />

Now let’s use it!

<!--- Use it! --->
<cfoutput>
   <cfloop array="#dataList#" index="i">  
      [#i#] => #isNumericList(i)#<br />
   </cfloop>
</cfoutput>

Results!

[456c] => false
 
[456] => true
 
[45c,] => false
 
[5,,,] => false
 
[565,651,34,643232,45] => true
 
[454,c,45,5454,32] => false
 
[121.45,43,565,1,1,2] => false
 
[43,54,65,1,] => false
 
[67,54,73,436,] => false
 
[,6565,656,77,32,3] => false
 
[] => false
 
[,43656] => false
 
[4365,  55,31,24,   5,   5  ,1,      34] => true