STR (SQL)
Synopsis
STR(number[,length[,decimals]])
Description
STR converts a numeric to the STRING format, truncating the numeric based on the values of length and decimals. The length argument must be large enough to include the entire integer portion of the number, and, if decimals is specified, that number of decimal digits plus 1 (for the decimal point). If length is not large enough, STR returns a string of asterisks (*) equal to length.
STR converts numerics to their canonical form before string conversion. It therefore performs arithmetic operations, removes leading and trailing zeros and leading plus signs from numbers.
If the number argument is NULL, STR returns NULL. If the number argument is the empty string (''), STR returns the empty string. STRING retains whitespace.
Arguments
number
An expression that resolves to a numeric. It can be a field name, a numeric, or the result of another function. If a field name is specified, the logical value is used.
length
An optional integer specifying the total length of the desired output string, including all characters (digits, decimal point, sign, blank spaces). The default is 10.
decimals
An optional integer specifying the number of places to the right of the decimal point to include. The default is 0.
Example
In the following example, STR converts numerics into a string:
SELECT STR(123),
STR(123,4),
STR(+00123.45,3),
STR(+00123.45,3,1),
STR(+00123.45,5,1)
The first STR function returns a string consisting of 7 leading blanks and the number 123; the seven leading blanks are because the default string length is 10. The second STR function returns the string “ 123”; note the leading blank needed to return a string of length 4. The third STR function returns the string “123”; the numeric is put into canonical form, and decimals defaults to 0. The fourth STR function returns “***” because the string length is not long enough to encompass the entire number as specified; the number of asterisks indicates the string length. The fifth STR function returns “123.4”; note that the length must be 5 to include the decimal digit.