Table of ContentsPreviousNext

Ispirer
Please, note, that Ispirer SQLWays 6.0 is no longer supported and provided to clients.
You can try out automated conversion of databases and applications with Ispirer Toolkit for free. Download free trial.
Check out the relevant toolkit documentation.
Ispirer Database Conversion overview.


Ispirer SQLWays Database Migration Software

Converting String to Number

This subsection describes functions, which convert string to number in various databases and their conversion by SQLWays.

TABLE 51. Converting String to Number
Database
Syntax
Description
Oracle
TO_NUMBER (exp1 [, 
exp2 [, exp3]] ) 
Converts exp1, a value of CHAR, VARCHAR2, NCHAR or NVARCHAR2 data type containing a number in format specified by the optional format model exp2, to a value of NUMBER data type.
Return type: NUMBER.
 
The exp3 specifies characters that are returned by number format elements:
� Decimal character
� Group separator
� Local currency symbol
� International currency symbol.
 
A number format model (exp2) is composed of one or more number format elements as listed in following:
Element
Example
Description
, (comma)
9,999
Returns a comma in the specified position. Multiple commas maybe specified.
Restrictions:
� A comma element cannot begin a number format model.
� A comma cannot appear to the right of a decimal character or period in a number format model.
. (period)
99.99
Returns a decimal point, which is a period (.) in the specified position.
Restriction: Only one period maybe specified.
$
$9999
Returns value with a leading dollar sign.
0
0999
Returns leading zeros.
9990
Returns trailing zeros.
 
 
9
9999
Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative.
Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
B
B9999
Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of "0"s in the format model).
C
C999
Returns in the specified position the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter in exp3).
EEEE
9.9EEEE
Returns a value using in scientific notation.
D
99D99
Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter in exp3. The default is a period (.).
Restriction: Only one decimal character maybe specify.
FM
FM90.9
Returns a value with no leading or trailing blanks.
 
 
G
9G999
Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter in exp3). Multiple group separators maybe specify in a number format model.
Restriction: A group separator cannot appear to the right of a decimal character or period.
L
L999
Returns in the specified position the local currency symbol (the current value of the NLS_CURRENCY parameter in exp3).
MI
9999MI
Returns negative value with a trailing minus sign (-). Returns positive value with a trailing blank.
Restriction: The MI format element can appear only in the last position of a number format model.
PR
9999PR
Returns a negative value in <angle brackets>. Returns a positive value with a leading and trailing blank.
Restriction: The PR format element can appear only in the last position of a number format model.
RN
RN
Returns a value as Roman numerals in uppercase. A value can be an integer between 1 and 3999.
 
 
rn
rn
Returns a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999.
S
S9999
Returns a negative value with a leading minus sign (-). Returns a positive value with a leading plus sign (+).
Restriction: The S format element can appear only in the first or last position of a number format model.
9999S
Returns a negative value with a trailing minus sign (-). Returns a positive value with a trailing plus sign (+).
TM
TM
"Text minimum". Returns (in decimal output) the smallest number of possible characters. This element is case-insensitive.
The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If output exceeds 64 characters, then Oracle automatically returns the number in scientific notation.
Restrictions:
� This element cannot precede with any other element.
� This element can follow only with 9 or E (only one) or e (only one).
 
 
U
U9999
Returns in the specified position the "Euro" (or other) dual currency symbol (the current value of the NLS_DUAL_CURRENCY parameter in exp3).
V
999V99
Returns a value multiplied by 10n (and if necessary, round it up), where n is the number of 9's after the "V".
X
XXXX
Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle rounds it to an integer.
Restrictions:
� This element accepts only positive values or 0. Negative values return an error.
� This element can precede only with 0 (which returns leading zeroes) or FM. Any other elements return an error. If you specify neither 0 nor FM with X, then the return always has 1 leading blank.
MySQL
CAST (exp1 AS 
UNSIGNED [INTEGER]) 
  
CAST (exp1 AS SIGNED 
[INTEGER])	 
Converts a value of exp1 to a value of UNSIGNED or SIGNED INTEGER data type.
 
If it uses numerical operations (like +) and one of the operands is unsigned as integer, the result will be unsigned. This can be overrided by using the SIGNED and UNSIGNED cast operators to cast the operation to a signed or unsigned 64-bit integer, respectively.


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