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.