Transposing First Character w/ Last Character in a Field

Last reviewed: October 9, 1995
Article ID: Q114498
The information in this article applies to:
  • Microsoft Visual FoxPro for Windows, version 3.0
  • Microsoft FoxPro for Windows, versions 2.5x, 2.6x
  • Microsoft FoxPro for MS-DOS, versions 2.0, 2.5x, 2.6x
  • Microsoft FoxPro for Macintosh, version 2.5x, 2.6a

SUMMARY

The code below shows how to switch the first character with the last character in a field for all records.

MORE INFORMATION

The following code demonstrates an easy way to accomplish this:

   * Creates an array containing the leftmost character
   SELECT LEFT(ALLTRIM(<fieldname>),1) FROM <table-name> ;
      INTO ARRAY <arrayname>

   * Replaces all the records in the field
   REPLACE ALL <fieldname> WITH RIGHT(ALLTRIM(<fieldname>),1)+ ;
      SUBSTR(ALLTRIM(<fieldname>),2,LEN(ALLTRIM(<fieldname>))-2) ;
      +<arrayname>(RECNO())

Before the commands are executed, a sample table may look like the following:

   <FIELDNAME>
   -----------
   A-0000-Z
   B-0-Y
   C-000000-X
   D-00000-W

After the commands are executed, it will look like:

   <FIELDNAME>
   -----------
   Z-0000-A
   Y-0-B
   X-000000-C
   W-00000-D

The first command takes the leftmost character and stores the information in an array for each record. This leftmost character must be stored in this manner; otherwise, the rightmost character will be substituted into the leftmost position in the REPLACE expression, which will cause the resulting records to contain the original rightmost character at the beginning and at the end.

These commands work with variable-length character fields. With a few modifications, this code can be quite flexible and useful for string- manipulation purposes and can be adapted to work with other types of fields.

REFERENCES

"Commands & Functions," version 2.0 (MS-DOS), pages C3-701 to C3-704, and C3-659 to C3-660

"Language Reference," version 2.5 (MS-DOS and Windows), pages L3-862 to L3-866, and L3-816 to L3-818


Additional reference words: FoxMac FoxDos FoxWin VFoxWin 2.00 2.50 2.50a
2.50b 2.50c 2.60 2.60a 3.00 change reverse alter
KBCategory: kbprg kbcode
KBSubcategory: FxprgGeneral


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: October 9, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.