How to Use UDF() to Modify Default Scientific Notation Format

MORE INFORMATION

The following program is a UDF() that can process a numeric value into the standard format expected by many customers who have expressed a need for an alternative format for scientific notation:

PARAMETERS   nPassedVal

* Program:   Num2sci.prg
*
* Purpose:   Convert FoxPro formatted numeric values requiring scientific
*            notation to the frequently requested n.nnnE+n format.
* Syntax:    NUM2SCI(nExpression)
* Arguments: nExpression is the numeric value to convert to the alternative
*            format.
* Returns:   Character

* Important notes about minimum and maximum ranges FoxPro will address:
* -------------------------------------------------------------------------
* SET DECIMALS makes no difference to any of this functionality.
* FoxPro will round values beginning at the fifteenth decimal place.
* Margin of error at 15 decimals or more is 3.410910030699E-15.
* The maximums for the STR() function are: STR(n,25,18).
*
* Any value of n.nE+19 or greater will resolve to scientific notation.
*
*   Ex: ALLTRIM(STR(1.234567890e+18,25,18))
*       Resolves to: "1234567890000000000.00000"
*   Ex: ALLTRIM(STR(1.234567890e+19,25,18))
*       Resolves to: ".1234567890000004000E+20"
*
* Any value passed with n.nE-16 or lower will resolve to equal zero.
*
*   Ex: ALLTRIM(STR(1.234567890e-15,25,18))
*       Resolves to: "0.000000000000001000"
*   Ex: ALLTRIM(STR(1.234567890e-16,25,18))
*       Resolves to: "0.000000000000000000"
*
* These are limitations in FoxPro that cannot be changed. This procedure,
* and therefore any data it processes, is limited to working within these
* constraints.
*
* -------------------------------------------------------------------------

* Stage 1: Convert to character
cConvStage1 = UPPER(ALLTRIM(STR(nPassedVal,25,18)))
* Find initial location of decimal point
nDecimalLoc = ATC(".", cConvStage1)
* Find 'E' beginning notation
nExpLoc     = RAT("E", cConvStage1)

IF nExpLoc = 0                           && No notation in resolved value.
  * Remove trailing zeros from character value
  DO WHILE SUBSTR(cConvStage1,LEN(cConvStage1),1) = '0'
    cConvStage1 = SUBSTR(cConvStage1,1,LEN(cConvStage1)-1)
  ENDDO
  cRetVal = SUBSTR(cConvStage1,1)    && Return entire number included.
decimals
ELSE
* Stage 2: Value deconstruction and element reformatting
* Swap positions of first two characters
  cConvStage2a = SUBSTR(cConvStage1,2,1)+"."
  * Portion between the decimal and the 'E'
  cConvStage2b = SUBSTR(cConvStage1,3,nExpLoc-3)
  * Decrement notation by 1
  cConvStage2c = "E+"+ALLTRIM(STR(VAL(SUBSTR(cConvStage1,nExpLoc+2))-1))

  * process portion between decimal and the 'E' to remove trailing Zeros
  DO WHILE SUBSTR(cConvStage2b,LEN(cConvStage2b),1) = '0'
    cConvStage2b = SUBSTR(cConvStage2b,1,LEN(cConvStage2b)-1)
  ENDDO

  * Stage 3: Value reconstruction
  cRetVal = cConvStage2a+cConvStage2b+cConvStage2c
ENDIF
RETURN cRetVal
				

To convert the value back to numeric, use the VAL() function against the character data NUM2SCI() returned.