PRB: SQL - SELECT with TRIM() Doesn't Trim Field Results

• Microsoft FoxPro for Windows, versions 2.5, 2.5a, and 2.5b
• Microsoft FoxPro for MS-DOS, versions 2.5, 2.5a, and 2.5b
• Microsoft FoxPro for Macintosh, version 2.5b

SYMPTOMS

When you use the SELECT command with the TRIM(), ALLTRIM(), or RTRIM() functions, the result is not trimmed.

CAUSE

When you include ALLTRIM(), TRIM(), or RTRIM() on a field or expression in the projection list, FoxPro cannot return to you a variable-length field (which is what it is being asked to do). Instead, it creates a column that is the length of the trimmed expression in order to prevent data loss.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

The following code demonstrates how the SELECT command treats a trimmed field or expression. This example uses the ALLTRIM() function.

   CLEAR ALL
   CLEAR

   CREATE TABLE testdbf ;
      (dbfname C(8),field C(10), type C(1), ;
      length N(3), precision N(2))

   INSERT INTO testdbf ;
      (dbfname,field,type,length,precision) ;
      VALUES ("test","testfld","C",10,0)

   *\\ SELECT does not respect the ALLTRIM().
   SELECT ALLTRIM(dbfname), field, type, length, precision ;
          FROM testdbf INTO ARRAY test
   DISPLAY MEMORY LIKE test

   *\\ SELECT does not respect the TRIM for the LEN
   *\\ of the field contents.
   SELECT SUBSTR(dbfname,1,LEN(TRIM(dbfname))), field, ;
          type, length, precision ;
          FROM testdbf INTO ARRAY test
   DISPLAY MEMORY LIKE test

   *\\ SELECT respects the hard-coded number for the length
   *\\ of the field contents.
   SELECT SUBSTR(dbfname,1,4), field, type, length, precision ;
          FROM testdbf INTO ARRAY test
   DISPLAY MEMORY LIKE test

   *\\ SELECT respects the variable that has pretrimmed the
   *\\ field contents.
   m.tablename = SUBSTR(dbfname,1,LEN(TRIM(dbfname)))
   SELECT m.tablename, field, type, length, precision ;
          FROM testdbf INTO ARRAY test
   DISPLAY MEMORY LIKE test