I was recently working with a need to compare two results from a single User Defined Function (UDF). On the surface, this is impossible since a function can only return one result. However, that limitation can be overcome if that result is a structure of all the results given in a set of calculations.

In the example below, I've really simplified a function into something that you wouldn't necessarily bother to return as a structure, but this is just to help get a down and dirty idea across without getting bogged down in a complex UDF.

Here we are trying to determine what the last day of the current month is and how many days until that date.

<cffunction name="fnMonthLastDay" access="public" returntype="struct">
   <cfargument name="SetDate" type="string" required="yes">
   <cfscript>
      var SetYear=DatePart("yyyy", SetDate);
      var SetMonth=DatePart("m", SetDate);
      if (ArrayLen(Arguments) GT 1)
      SetYear=Arguments[2];

      LastDay=StructNew();
      LastDay.LastDate=DateAdd("d", -1, DateAdd("M", 1, CreateDate(SetYear, SetMonth, 1)));
      LastDay.LastDays=DateDiff("d", Now(), LastDay.LastDate);

      return LastDay;
   </cfscript>
</cffunction>

<cfset LastDay=fnMonthLastDay(Now())>
<cfdump var="#LastDay#">

The UDF grabs the year and month from the date value passed, advances it to the first of the following month, then subtracts one day from it to determine the last day. Then the UDF calculates the difference between the two dates and returns a structure. We set the result against a variable, in case we want to use it more than once and end by dumping the structure LastDay with two elements (LastDate, and LastDays).

If you find this post useful please leave a comment and let me know how you used the information.

In sharp contrast to how make SQL deal with a list value, ColdFusion is very versatile. Although the SQL version of this example can be very confusing to look at, the ColdFusion version is very short and easy to understand. What programmers forget to do sometimes, is give more data manipulation to the SQL server. Most of the time the server is just sitting there waiting for the formatted data to be sent to it from ColdFusion, or waiting for a query request. We tend to give everything to ColdFusion to do, causing our processes to slow down while our SQL server is barely used.

Here is how list values can be extracted in ColdFusion:

<cfset NameConcat="John.Q.Public">

<cfset NameFirst=ListGetAt(NameConcat, 1, ".")>
<cfset NameLast=ListGetAt(NameConcat, 3, ".")>
<cfset NameMI=ListGetAt(NameConcat, 2, ".")>

Very short, very easy to understand, ListGetAt just returns the substring from the position in the list as defined.

If you find this post useful please leave a comment and let me know how you used the information.

I was working with a database that was recording personnel data with first names in one record and last names in another with middle initials in another. Nothing unusual about that, but I wanted to try something different. The last names had already been stripped of all punctuation, hyphens, and dashes; this provided an opportunity to store them in one record. The pulled record could then be given to ColdFusion as a three item list, or let SQL do the work for a change.

In this example I am storing the names in a database table, in one record in the format "John.Q.Public". The plan is to show how to use SQL to divide this record into three pieces; NameFirst, NameMI, and NameLast. The trick is using a character that will divide the three names into non-repeating ranges. Since all punctuation was already removed from this data I used ".", but you could just as easily use the pipe "|" character or whatever works for you.

We use CHARINDEX to determine the position of the "." marks and get the number that represents the place it occurs in the record. This is variable because everyone's names are not the same length. SQL does not have great list tools like ColdFusion, so we have to use positional manipulation. To get the last name, we will use CHARINDEX again, but by reversing the record we can get the position of the last name. We then use LEFT and RIGHT to set the name ranges based on the values returned by CHARINDEX. The middle initial is a little more tricky. Easiest way is to PATINDEX the two decimal points surrounding the middle inital and then use the SUBSTRING to pull out one letter.

DECLARE @SQL VARCHAR(255);
SET @SQL='John.Q.Public';

SELECT   @SQL AS NameConcat,
         Left(@SQL, CHARINDEX('.', @SQL) - 1) AS NameFirst,
         Right(@SQL, CHARINDEX('.', Reverse(@SQL)) - 1) AS NameLast,
         SUBSTRING(@SQL, PATINDEX('%._.%', @SQL) + 1, 1) AS NameMI

In this example I've given you real data to use by defining a test value. It divides out the first name and last name from a concatenated record separated by a decimal point "." and stored as a single record. LEFT and RIGHT have had one subtracted from their values to omit the decimal point and SUBSTRING has one added to its value to omit the leading decimal point.

If you find this post useful please leave a comment and let me know how you used the information.

Ever tried to rename a directory to the same name but with different letter case through CFDIRECTORY? Does not work to well on a Microsoft Windows platform. Why? Unlike Linux, Windows sees the names "MyDirectory", "MYDIRECTORY", and "mydirectory" as the same. Here is a Java call through CFSCRIPT that will allow you to rename a folder between the different cases.

<cfscript>
   src=CreateObject("java", "java.io.File").init("c:\mydirectory");
   dest=CreateObject("java","java.io.File").init("c:\MyDirectory");
   src.RenameTo(dest);
</cfscript>

Remember, this works because Java is platform independent. For this reason, unlike CFDIRECTORY on a Windows machine, it won't see the three directory names in the paragraph above as the same item.

Obviously, you wouldn't want to hard code a directory rename. After all, it would run once, rename the directory and never run again. For this reason, for usability just substitute the two absolute folder names with variables that are selected through the process of your choice and design.

If you find this post useful please leave a comment and let me know how you used the information.