It looks as if this month's column is all about functions. In the questions and answers below we'll look at RandRange(), ListLen(), ListAppend(), GetTickCount(), and the ever-elusive Soundex() functions.

Q:I need to build a search engine for our employee directory that will return some results even if the user doesn't know exactly how someone's last name is spelled (I think they call this a "fuzzy" search). How can I do this?

A:There are two (probably more) ways to do a "fuzzy" search, both of which have little to do with CF and everything to do with SQL. The first and most obvious solution is to use the LIKE operator with wild cards in your SQL (see Listing 1). This first example uses a wild card at the end of the search text. This way, if the user knows the first few letters of the person's last name, it'll return all those names that begin with those characters. Notice the use of "LIKE" instead of the "=" operator.

Another way is to search for words that sound alike, such as "Michael" and "Michelle." This can be done using the Soundex() function (see Listing 2). Please note that Soundex() is not a CF function; it's a SQL function that's supported by most database engines such as SQL Server, Oracle, and Informix (not Access!). Soundex() converts the string into its phonetic representation (way beyond the scope of this column) so that two different strings may have the same "Soundex" value.

Q:I know the CF Server debug settings let me find out how long it took to process an entire page, but is there a way to find out how long it took to process a particular block of code within a page?

A: Yes, that's what GetTick-Count() was built to do! It's really simple (see Listing 3). Set a variable to the value of GetTickCount() at the beginning of the code you want to test, and another one equal to GetTickCount at the end of the code. Then simply compare the two values to determine how long it took (the value will be in milliseconds).

Q:In the November issue (CFDJ, Vol. 2, issue 11), you showed us how to randomly display one record from a query. How would I show multiple (say four) records from a query without displaying the same record more than once?

A:Great question! There are many ways to accomplish this, including right at the database level through SQL (this is actually the preferred method but the syntax will be specific to your RDBMS). For example, you can use the ORDER BY RAND clause with an Oracle database. However, here's one way to do it using CF (see Listing 4) that will work with any database.

First, let's retrieve all the potential records from our database. Notice the CACHEDWITHIN attribute to make sure this query doesn't execute every time for every user. Next, I create a local variable, MyList, with a value of "". This variable will ultimately hold a list of the records displayed. Since you want to display only four records, we'll do a CFLOOP that will execute until there are four items in our list of displayed records. Inside the loop we need to pick a random record using RandRange(), check to see if it's already been shown using ListFind(), and, if not, add it to the list using ListAppend(). Last, we'll display that record using array syntax on the query variable.

Please send your questions about ColdFusion (CFML, CF Server, or CF Studio) to AskCFDJ@syscon.com.

• Source Code