Conversion Functions in SQL

There may be times when the SQL query was expecting some data type but it got another. In that case type conversion is done by SQL. This is implicit type conversion. However, sometimes the programmer explicitly converts one data type into another in a query. This is known as explicit type conversion. Both of these in detail are given as follows:

Implicit Data Type Conversion

In implicit data type conversion, the SQL programmer does not specify anything. Rather the system converts one type of data to another as per its requirements. For example - a numeric data type can be converted to character if required and vice versa.

Implicit DataType Conversion only occurs if the data types used in the query are valid so that the system can recognise them and convert them as required. This is not possible with invalid or wrongly provided data types.

Explicit Data Type Conversion

If the programmer wants,they can explicitly convert data types from one form to another. There are SQL functions provided  for this express purpose. These SQL functions are −

TO_CHAR

This function is used to explicitly convert a number or date data type to char.

The syntax of this function is given as follows −

TO_CHAR(number,format,parameters)

This function changes number to char with the specific format as provided according to the syntax. The parameters can be used to specify decimal characters, group separators etc.

For example −

SELECT CHAR(sysdate, “Month DD,YYYY”) FROM DUAL;

This returns the system date in the form of 31 July, 2018

TO_NUMBER

This function is used to explicitly convert a string to number. If the string to be converted does not contain numeric characters then TO_NUMBER shows an error.

The syntax of this function is given as follows −

TO_NUMBER(number,format,parameters)

This function changes the given string to number with the specific format as provided according to the syntax.

The parameters and format are optional so to convert a string to a number

For example,

TO_NUMBER (‘353.78’);

This returns the string 353.78 in numerical format.

TO_DATE

This function takes character values and returns the output in the date format.

The syntax of this function is given as follows −

TO_DATE(number, format, parameters)

This function changes the given string to number with the specific format as provided according to the syntax.

SELECT TO_DATE(‘2018/07/31’,’yyyy/mm/dd’) FROM DUAL;

This takes the values in character format and returns them in date format as specified.