What are conversion functions in SQL? | OCA Preparation-7

In my previous articles i have given multiple examples of different functions in SQL. In this article i would like to focus on conversion functions in SQL with real life industry examples. There are so many data conversion functions in SQL which are widely used in database management systems and real life industry scenarios. There are two types of conversion happened in oracle – Implicit conversion of data and explicit conversion of data.

What We will find in this article?

1. Types of Conversions in Oracle
2. Oracle Conversion functions with real life examples

Types of Conversion functions in Oracle :

The oracle will use two types of conversions while processing the data.

1.Implicit Conversion : The Implicit conversion is done by implicitly by oracle parser rather than giving any function for converting data from one datatype to other datatype. The oracle will do implicit conversion directly.

Real life Example :

Select * from Employee where Employee_id=’102′ and Hire_date=’11-Oct-2017′;

The above statement will implicitly convert employee id from string to number and higher date also from string to number.

2.Explicit Conversion: The explicit conversion is done by the user by using multiple conversion functions. Although the implicit data conversion is there user need to use the explicit data conversion to ensure the reliability of the SQL statements. You can convert explicitly the numbers and dates. The following diagram will give you the overview of explicit conversion of different datatypes.

The To_Char function is key function which is used to convert number or date in to character.In next session i would like to give you multiple examples of data conversions.

Oracle Conversion functions with real life examples :

In this section i would like to provide details on To_char() function with date conversion. You can refer the following direct article before going through the following examples.

Scenario 1 : To convert sysdate in multiple formats

SELECT TO_CHAR(SYSDATE,’dd.mm.yyyy’) FROM DUAL;

The above query will give you output in simple dd.mm.yyyy format like ’07-12-2020′

SELECT TO_CHAR(SYSDATE,’dd-mm-yyyy hh:mi:ss AM’) FROM DUAL;

The above query will give you date as well as time in AM/PM format.The output of the query will be ’07-12-2020 12:12:13 PM”

SELECT TO_CHAR(SYSDATE,’dd-mm-yyyy hh24:mi:ss PM’) FROM DUAL;

The above query will give you date as well as time in AM/PM 24 hours format. The output of the query will be ’07-12-2020 22:12:13′.

You can change the date or convert date in multiple formats using the same. We can check and retreive the data day wise, monthwise or yearwise. Examples :

-list all the employees who employeed in 2020
SELECT * FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,’yyyy’)=’2020′;

–list all the employees who employeed in may
SELECT * FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,’mm’)=’05’;

SELECT * FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,’fmmm’)=’5′;

you can use fm ( Format) if you want to put only ‘5’ because fm remove 0 and spaces.

Scenario 2 : Using to_char function with numbers.

User can use the to_char function to convert the numbers in to the character format. You can convert the numbers in to to_char function in multiple formats.

SELECT TO_CHAR(1234) FROM DUAL;

The above statement will convert 1234 in to char format.

SELECT TO_CHAR(1111,’9,999′) FROM DUAL;

The above query will convert 1111 in to format 1,111 and output will be 1,111

SELECT TO_CHAR(1111,’$9,999′) FROM DUAL;

The above query will give you output as $1111

You can check by running following queries on liveoracle.com

These are above some important examples of converting numbers in different characters.

Scenario 3 :Using the to_number function for converting the string in numbers.

In this section i would like to give you different examples for converting string in to numbers.

SELECT TO_NUMBER(‘1,111′,’9,999’) FROM DUAL;

SELECT TO_NUMBER(‘$1,111′,’$9,999’) FROM DUAL;

SELECT TO_NUMBER(’89’) FROM DUAL;

SELECT TO_NUMBER(‘1,434′,’9G999’) FROM DUAL;

Scenario 4 :Using the to_date function for converting the string in date format.

This section will give you multiple examples for converting the string in date format.

SELECT TO_DATE(’11-10-2020′,’dd-mm-yyyy’) FROM DUAL;

SELECT TO_DATE(‘11.10.2020′,’dd.mm.yyyy’) FROM DUAL;

SELECT TO_DATE(’11.october.2020′,’dd.month.yyyy’) FROM DUAL;

These are above function which are most important conversion functions in SQL with examples. I hope you like this article on conversion functions in SQL with multiple examples. If you like this article or if you have any issues or concerns with same kindly comment in comments section.