Conversion Functions in Oracle SQL

  3shTech (trainings@trishtechnology.com)

String Functions

Numeric Functions

Group Functions

Date Functions

Analytical Functions

Conversion Functions

These functions are used to convert the values from one type to anther type.

Types:-

·       TO_NUMBER

·       TO_CHAR

·       TO_DATE

·       Other Conversion Functions

·       BIN_TO_NUM

·       CHARTOROWID

·       ROWIDTOCHAR

 
Conversion Rules between data types:-
 

Data Type	Char	Num	Date
Char	—	Yes	Yes
Num	Yes	—	Not Applicable
Date	Yes	Not Applicable	—

 
TO_NUMBER
 

·       To_Number will convert a char or varchar into number.

SQL>TO_NUMBER (char, [format]);
Example:
SQL>SELECT TO_NUMBER (‘1,23,456.56′,’9,99,999.99’) FROM DUAL;

 
TO_CHAR:-

·       TO_Char function will convert a number or date to character string.

Syntax: For number to char.

For date to char: – TO_CHAR (date, [format]).
 

Example:–
SQL>SELECT TO_CHAR (SYSDATE,’day’) FROM DUAL;

SQL>SELECT TO_CHAR (123456,’99, 99,999.99′) FROM DUAL;

 
TO_DATE
 

·       To_date function will convert a char or varchar value into date value.

Syntax: – TO_DATE (char, [format])

Example:–
SQL>SELECT TO_DATE (12232012,’mmddyyyy’) FROM DUAL;

SQL>SELECT TO_DATE (‘02112012′,’ddmmyyyy’) FROM DUAL;

SQL>SELECT TO_DATE (‘20121102′,’yyyymmdd’) FROM DUAL;

 
BIN_TO_NUM
 

BIN_TO_NUM will convert the binary value to its numerical equivalent.

Syntax: –
SQL>BIN_TO_NUM (binary_bits)
Example:– Binay bits passed are used to fetch the equivalent numeric value.
SQL>SELECT Bin_to_num (1, 0, 1) FROM DUAL;

 

·       If all the bits are zero then it produces zero.

·       If all the bits are null then it produces an error.

 
CHARTOROWID
 

·       This will convert a character string to act like an internal oracle row identifier or rowid.

 
ROWIDTOCHAR
 

·       This will convert an internal oracle row identifier/rowid to character string.

Happy Learning!!

Thank you!! 😊