View video tutorial
ORACLE Conversion Functions
ORACLE
Oracle SQL has several conversion functions that explicitly convert data from one type to another.
ORACLE Conversion Functions
➔ Primary Oracle Conversion Functions:
- TO_CHAR: Converts a number or date value to a character string with optional formatting.
- TO_NUMBER: Converts a character string to a numeric value with optional formatting.
- TO_DATE: Converts a character string to a date value with the required formatting.
- CAST: Converts a value from one data type to another data type.
Syntax
TO_CHAR(value [, format_mask] [, nls_language])
➔ value: The number, date, or timestamp to convert.
➔ format_mask: (optional) Specifies the output format.
➔ nls_language: (optional) Specifies characters or symbols for a specific region.
Example: TO_CHAR with Date
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;
Example:TO_CHAR with Timestamps
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH:MI:SS.FF3 TZR') FROM DUAL;
Example:TO_CHAR with Numbers
/* with a separator (comma) */
SELECT TO_CHAR(12345.67, '99,999.99') FROM DUAL;
/* Adds leading zeros */
SELECT TO_CHAR(123, '00000') FROM DUAL;