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

ID: Q112701

The information in this article applies to:

- 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

Additional reference words: FoxMac FoxDos FoxWin 2.00 2.50 2.50a 2.50b SQL KBCategory: kbprg kbprb KBSubcategory: FxprgSql

Last Reviewed: June 27, 1995 © 1999 Microsoft Corporation. All rights reserved. Terms of Use.