Table of ContentsPreviousNext

Ispirer             Ispirer


Ispirer SQLWays Database Migration Software

Converting Expression to String

This subsection describes functions that allow converting expression to a string in various databases and their conversion by SQLWays.

TABLE 53. Converting Expression to a String
Database
Syntax
Description
Oracle
TO_CHAR(n [, fmt [, 
`nlsparam']]) 
Converts n of NUMBER data type to a value of VARCHAR2 data type, using the optional number format fmt.
 
If fmt is omit, then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits.
 
The 'nlsparam' specifies these characters that are returned by number format elements:
- Decimal character
- Group separator
- Local currency symbol
  • International currency symbol
This argument can have this form:
'NLS_NUMERIC_CHARACTERS = ''dg''
NLS_CURRENCY = ''text''
NLS_ISO_CURRENCY = territory '
The characters d and g represent the decimal character and group separator, respectively. They must be different single-byte chara�cters. Note that within the quoted string, you must use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.
If 'nlsparam' or any of the parameters are omitted, this function uses the default parameter values for session.
TO_CHAR (nchar | clob | 
nclob) 
Converts NCHAR, NVARCHAR2, CLOB, or NCLOB data to the database character set.
Microsoft SQL Server
STR (fl_exp [, length [, 
decimal]]) 
Returns character data converted from numeric data.
fl_exp - is an expression of approximate numeric (float) data type with a decimal point.
length - is the total length, including decimal point, sign, digits, and spaces. The default is 10.
decimal - is the number of places to the right of the decimal point.
 
Return type - char.
 
If supplied, the values for length and decimal parameters to STR should be positive. The number is rounded to an integer by default or if the decimal parameter is 0. The specified length should be greater than or equal to the part of the number before the decimal point plus the number's sign (if any). A short fl_exp is right-justified in the specified length, and a long fl_exp is truncated to the specified number of decimal places.
 
For example, STR(12,10) fields the result of 12, which is right-justified in the result set. However, STR(1223, 2) truncates the result set to **.
IBM DB2
CHAR ( int_exp) 
Returns a fixed-length character string representation of an integer number. If argument can be null, the result can be null. If argument is null, the result is the null value.
 
int_exp - is the expression that returns a value that is an integer data type (either SMALLINT, INTEGER or BIGINT).
 
The result is the character string represent�tation of the argument in the form of an SQL integer constant. The result consists of n characters that are the significant digits that represent the value of the argument with a preceding minus sign if the argument is negative. It is left justified.
� If the first argument is a small integer: The length of the result is 6. If the number of characters in the result is less than 6, then the result is padded on the right with blanks to length 6.
� If the first argument is a large integer:
The length of the result is 11. If the number of characters in the result is less than 11, then the result is padded on the right with blanks to length 11.
� If the first argument is a big integer: The length of the result is 20. If the number of characters in the result is less than 20, then the result is padded on the right with blanks to length 20.
CHAR ( character_exp [, 
integer] ) 
Returns a fixed-length character string representation of a character string, if the first argument is any type of character string. If the first argument can be null, the result can be null. If the first argument is null, the result is the null value.
 
character_exp - is an expression that returns a value that is CHAR, VARCHAR, LONG VARCHAR, or CLOB data type.
integer - is the length attribute for the resulting fixed length character string. The value must be between 0 and 254.
 
If the length of the character-expression is less than the length attribute of the result, the result is padded with blanks up to the length of the result. If the length of the character-expression is greater than the length attribute of the result, truncation is performed. A warning is returned unless the truncated characters were all blanks and the character-expression was not a long string (LONG VARCHAR or CLOB).
 
CHAR ( decimal_exp [, 
decimal_ch] ) 
Returns a fixed-length character string representation of a decimal number, if the first argument is a decimal number. If the first argument can be null, the result can be null. If the first argument is null, the result is the null value.
 
decimal-exp - is an expression that returns a value that is a decimal data type. If a different precision and scale is desired, the DECIMAL scalar function can be used first to make the change.
decimal-ch - specifies the single-byte character constant that is used to delimit the decimal digits in the result character string. The character cannot be a digit, plus ('+'), minus ('-') or blank. The default is the period ('.') character.
 
The result is the fixed-length character-string representation of the argument. The result includes a decimal character and p digits, where p is the precision of the decimal-exp with a preceding minus sign if the argument is negative. The length of the result is 2+p, where p is the precision of the decimal-exp. This means that a positive value will always include one trailing blank.
CHAR ( fl_exp [, 
decimal_ch] ) 
Returns a fixed-length character string representation of a double-precision floating-point number, if the first argument is a DOUBLE or REAL. If the first argument can be null, the result can be null. If the first argument is null, the result is the null value.
 
fl_exp - is an expression that returns a value that is a floating-point data type (DOUBLE or REAL).
decimal_ch - specifies the single-byte character constant that is used to delimit the decimal digits in the result character string. The character cannot be a digit, plus (+), minus (-), or blank character. The default is the period (.) character.
 
The result is the fixed-length character-string representation of the argument in the form of a floating-point constant. The length of the result is 24. If the argument is negative, the first character of the result is a minus sign. Otherwise, the first character is a digit. If the argument value is zero, the result is 0E0. Otherwise, the result includes the smallest number of characters that can represent the value of the argument so that the mantissa consist of a single digit other than zero followed by the decimal-character and a sequence of digits. If the number of characters in the result is less than 24, the result is padded on the right with blanks to length 24.
Sybase Adaptive Server Anywhere
STR (fl_exp [, length [, 
decimal]]) 
Returns the string equivalent of a number.
 
fl_exp - is any approximate numeric (float, real, or double precision) expression.
length - is the number of characters to be returned (including the decimal point, all digits to the right and left of the decimal point, and blanks). The default is 10.
decimal - is the number of decimal digits to be returned. The default is 0.
 
If the integer portion of the number cannot fit in the length specified, then the result is a string of the specified length containing all asterisks. For example, the following statement returns *** :
SELECT STR( 1234.56, 3 )
Sybase Adaptive Server Enterprise
STR (fl_exp [, length [, 
decimal]]) 
Returns the character equivalent of the specified number.
fl_exp - is any approximate numeric (float, real, or double precision) column name, variable, or constant expression.
length - sets the number of characters to be returned (including the decimal point, all digits to the right and left of the decimal point, and blanks). The default is 10.
decimal - sets the number of decimal digits to be returned. The default is 0.
 
� STR, a string function, returns a character representation of the floating point number.
length and decimal are optional. If given, they must be non-negative. STR rounds the decimal portion of the number so that the results fit within the specified length. The length should be long enough to accom�modate the decimal point and, if negative, the number's sign. The decimal portion of the result is rounded to fit within the specified length. If the integer portion of the number does not fit within the length, however, STR returns a row of asterisks of the specified length.
For example: select str(123.456, 2, 4)
A short appr_num is right justified in the specified length, and a long appr_num is truncated to the specified number of decimal
places.
� If appr_num is NULL, returns NULL.


Table of ContentsPreviousNext
Ispirer             Ispirer
Copyright © 1999-2015 Ispirer Systems Ltd. Ispirer and SQLWays are trademarks of Ispirer Systems Ltd. All other product names may be trademarks of the respective companies. All rights reserved.