Tod means Fox

It’s been said that if you can’t do something 3 different ways in FoxPro, then it can’t be done. I’m not sure where this phrase originated, but it’s stuck with me over the years. With options though, come responsibility. Yes, you can often solve a problem using several methods, but your job as a developer is to pick the most reliable and best performing of the options available. This could mean making a choice to use the REPLACE statement instead of SQL-UPDATE, MEMLINES/MLINE over ALINES, or TRANSFORM instead of STR.

Trimmed conversion of number n

For example, run this code from a program file a few times over. They are functionally equivalent, producing a trimmed string conversion of a number n:

clear
nSec = SECONDS()
FOR n = 1 TO 100000
  lc = ALLTRIM(STR(n))
NEXT
? "Run 1 with STR(): "
?? SECONDS() - nSec
 
nSec = SECONDS()
FOR n = 1 TO 100000
  lc = TRANSFORM(n)
NEXT
? "Run 2 with TRANSFORM(): "
?? SECONDS() - nSec
 
nSec = SECONDS()
FOR n = 1 TO 100000
  lc = CAST(n as Varchar(10))
NEXT
? "Run 3 with CAST: "
?? SECONDS() - nSec

You will notice right away that using CAST is by far the fastest (on average 2.5 times faster than ALLTRIM(STR())). Transform is a little faster than ALLTRIM(STR()) as well (on average 1.3 times faster using 100000 iterations as a test). If you reduce the number of iterations, this ratio reduce until finally ALLTRIM(STR()) begins to outperform TRANSFORM().

CAST is new to VFP9, so it is likely that it hasn’t made its way into a lot of your code — but perhaps some refactoring is in order! Especially in loops with many iterations or in SQL statements.

nLength conversion of number n

In the previous example, I returned a trimmed string of a number n. Consider that you want your numeric transformation to be of a particular size. What’s faster now?:

clear
nSec = SECONDS()
FOR n = 1 TO 1000000
  lc = STR(n,10,2)
NEXT
? "Run 1 with STR(): "
?? SECONDS() - nSec
 
nSec = SECONDS()
FOR n = 1 TO 1000000
  lc  = TRANSFORM(n,"9999999.99")
NEXT
? "Run 2 with TRANSFORM(): "
?? SECONDS() - nSec
 
nSec = SECONDS()
FOR n = 1 TO 1000000
  lc = CAST(n as Character(10))
NEXT
? "Run 3 with CAST: "
?? SECONDS() - nSec

Again, CAST is faster than both STR and TRANSFORM (on average 2.7 times faster). Transform (which surprised me) still outperforms STR() (at 100,000 iterations, the ratio on average was still about 1.3). But take heed! The last example here isn’t functionally equivalent: CAST will left-align the results, while STR and TRANSFORM right-align the results. STR and TRANSFORM will round decimals to the specified amount, CAST will not.

Obviously, there are many things you can do with TRANSFORM that you cannot do with either STR or CAST. In most cases, however, CAST seems to be the way to go. Using ‘Varchar’ in CAST trims your string, using ‘Character’, does not. TRANSFORM automatically produces a TRIMed string as well, unless you specify a format code.

I like the new CAST function in VFP9. I got used to using it while writing code to interact with SQL Server. But I soon found CAST sneaking into my code in various other places — often replacing ALLT(STR()) and TRANSFORM.