Snippet Name: TO_NUMBER
Description: Converts a string to a value of NUMBER datatype.
The string can be a BINARY_FLOAT or BINARY_DOUBLE value or a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype containing a number in the format specified by the optional format model fmt.
This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.
Also see:
» Add PSOUG Search to SQL Developer
» Converting Rows to Columns
» UNISTR
» TRANSLATE
» TO_YMINTERVAL
» TO_TIMESTAMP_TZ
» TO_TIMESTAMP
» TO_SINGLE_BYTE
» TO_NCLOB
» TO_NCHAR
» TO_MULTI_BYTE
» TO_LOB
» TO_DSINTERVAL
» TO_DATE
» TO_CLOB
» TO_CHAR
» TO_BINARY_FLOAT
» TO_BINARY_DOUBLE
» TIMESTAMP_TO_SCN
» SCN_TO_TIMESTAMP
» ROWIDTONCHAR
» ROWIDTOCHAR
» REFTOHEX
» RAWTONHEX
» RAWTOHEX
» NUMTOYMINTERVAL
» NUMTODSINTERVAL
» HEXTORAW
» DECOMPOSE
» CONVERT
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 05th, 2009
|
TO_NUMBER(expr [, fmt [, 'nlsparam' ] ])
TO_NUMBER(<binary_float | binary_double | number>,
'<hex mask>') RETURN <binary_float | binary_double | number>;
-- convert character string data into a number:
UPDATE employees SET salary = salary +
TO_NUMBER('100.00', '9G999D99')
WHERE last_name = 'Perkins';
-- Note the 'nlsparam' argument in this function
SELECT TO_NUMBER('-AusDollars100','L9G999D99',
' NLS_NUMERIC_CHARACTERS = '',.''
NLS_CURRENCY = ''AusDollars''
') "Amount"
FROM DUAL;
Amount
----------
-100
-- convert a HEX to FLOAT
SELECT TO_NUMBER('0A', 'XX')
FROM dual;
SELECT TO_NUMBER('1F', 'XX')
FROM dual;
-- convert a HEX to DECIMAL
SELECT TO_NUMBER(100000,'XXXXXXXX')
FROM dual; |
