TRANSFORM( ) Function

  • Article

Returns a character string from an expression in a format determined by a format code.

Note

Using TRANSFORM( ) with a large negative number that contains a decimal might result in numeric overflow when that number is passed in as a memory variable. You should use the STR( ) function instead with such numbers.

TRANSFORM(eExpression, [cFormatCodes])

Parameters

  • eExpression
    Specifies the expression to format.

  • cFormatCodes
    Specifies one or more format codes that determine how to format the expression.

Note

The following table lists the available format codes for cFormatCodes.

cFormatCodes

Description

@B

Left-justifies Numeric data within the display region.

@C

Appends a CR to positive Currency or Numeric values to indicate a credit.

@D

Converts Date and DateTime values to the current SET DATE format.

@E

Converts Date and DateTime values to a BRITISH date format.

@L

Pads numeric data with leading zeroes.

@R

The transformation uses a format mask. The mask characters are not stored to the transformed value. Use only with character or numeric data. Mask characters include:

  • 9 or #   Represents a character or number.

  • !   Converts lower-case letters to uppercase letters.

Note

Visual FoxPro does not display specified mask characters that exceed the converted string length of the passed expression.

@T

Trims leading and trailing spaces from character values.

@X

Appends DB to negative currency or numeric values to indicate a debit.

@YL

Use Long Date system setting.

@YS

Use Short Date system setting.

@Z

If 0, converts currency or numeric values to spaces.

@(

Encloses negative currency or numeric values in parentheses.

@^

Converts currency or numeric values to scientific notation.

@0

Converts numeric or currency values to their hexadecimal equivalents. The numeric or currency value must be positive and less than 4,294,967,296.

@!

Converts an entire character string to uppercase.

@$

Adds the current currency symbol specified by SETCURRENCY to currency and numeric values. By default, the symbol is placed immediately before or after the value. However, the currency symbol and its placement (specified with SET CURRENCY), the separator character (specified with SET SEPARATOR) and the decimal character (specified with SET POINT) can all be changed.

X

Specifies the width of character values. For example, if cFormatCodes is 'XX', 2 characters are returned.

Y

Converts logical True (.T.) and False (.F.) values to Y and N, respectively.

!

Converts a lowercase character to uppercase in the corresponding position in a character string.

.

Specifies the decimal point position in currency and numeric values.

,

Separates digits to the left of the decimal point in currency and numeric values.

9

Specifies width of numeric values.

For example, if cFormatCodes is '999.99', the numeric characters are formatted with three characters to the left of the decimal point, and two characters to the right.

Note

When specifying cFormatCodes for numeric values, the format code must be at least the size of the value you want to display.

The following example shows the results when the format code is missing, less than, and equal to the size of the value you want to display.

CREATE CURSOR myCursor (col1 n(5,2))
INSERT INTO myCursor VALUES (-555.5) 
* No format code and returns **.**
? TRANSFORM(myCursor.col1 )
* A format code 1 less than value returns ***.**
? TRANSFORM(myCursor.col1,'999.99' )
* A format code same size as value returns -556.00
? TRANSFORM(myCursor.col1,'9999.99' )

When the expression value is longer than the width of the numeric field, Visual FoxPro forces the value to fit by performing the following steps:

  • Truncate decimal places and round the remaining decimal portion of the field.

  • If the value does not fit, store the field contents using scientific notation.

  • If the value still does not fit, replace the field contents with asterisks.

For more information about format codes, see InputMask Property and Format Property.

If you omit cFormatCodes, Visual FoxPro performs a default transformation on eExpression. The following table describes the transformation performed for each data type eExpression can assume.

Data type

Transformation description

Blob

Outputs character string representation of the binary value without the hexadecimal prefix. For example, the following line of code outputs the character string "FEOAF2":

TRANSFORM(0hFE0AF2)

For binary values, TRANSFORM( ) disregards format codes except for those that restrict width. For example, the following lines of code output "FE0A":

TRANSFORM(0hFE0AF2, "XXXX")
TRANSFORM(0hFE0AF2, "9999")
TRANSFORM(0hFE0AF2, "####")

For those format codes that restrict width, the maximum output length is 255 characters.

Character

Performs no transformation.

Currency

Transformation is determined by the settings specified in the Regional tab of the Options dialog box.

Date

Performs a DTOC( ) transformation on the date.

DateTime

Performs a TTOC( ) transformation on the date time.

General

Returns "Gen" if the General field contains an object or "gen" if the General field does not contain an object.

Logical

Transforms logical True (.T.) and False (.F.) values to the character strings ".T." and ".F." respectively.

Memo

Performs no transformation.

Numeric (includes Double, Float, or Integer data types)

Removes trailing zeros from the decimal portion of a numeric value when all the numbers following the decimal point are zeros.

If the numeric value is a whole number, a decimal point is not included in the transformed value, for example, 4.0 is transformed to 4.

If the numeric value is less than one but greater than negative one, zero is included before the decimal point, for example, .4 is transformed to 0.4.

Object

Returns the character string "(Object)".

Varbinary

Outputs character string representation of the binary value without the hexadecimal prefix. For example, the following line of code outputs the character string "FEOAF2":

TRANSFORM(0hFE0AF2)

For binary values, TRANSFORM( ) disregards format codes except for those that restrict width. For example, the following lines of code output "FE0A":

TRANSFORM(0hFE0AF2, "XXXX")
TRANSFORM(0hFE0AF2, "9999")
TRANSFORM(0hFE0AF2, "####")

Return Value

Character data type. TRANSFORM( ) returns character string from an expression in a format determined by a format code.

Example

STORE 12.34 TO gnPrice
CLEAR
? TRANSFORM(gnPrice, '$$$$.99')  && Displays $12.34
? TRANSFORM(_SCREEN)   && Displays (Object)

See Also

Reference

DTOC( ) Function

TTOC( ) Function

InputMask Property

Other Resources

Functions

Language Reference (Visual FoxPro)