Microsoft KB Archive/112701

{| = PRB: SQL - SELECT with TRIM Doesn't Trim Field Results =
 * width="100%"|

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.

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
 * }