Conversion Function in SQL

Data type conversion in SQL ensures accurate query results by allowing different formats (numbers, text, dates, etc.) to be correctly interpreted and manipulated. Conversion functions such as TO_CHAR, TO_NUMBER, and TO_DATE are commonly used for this purpose.

Types of Data Type Conversion

There are two main types of data type conversion in SQL.

• Implicit Data Type Conversion: Automatic conversion of one data type to another by SQL during query execution.
• Explicit Data Type Conversion: Done by the user when SQL can’t convert automatically or when precise control is needed.

Implicit Data-Type Conversion

Implicit data type conversion, also known as automatic type casting, occurs when SQL automatically converts one data type to another without requiring any intervention from the user.

The DBMS does this whenever it detects a need for the conversion, based on the context of the operation.

Example of Implicit Data Type Conversion

Consider the following example where we retrieve employees whose salary is greater than 15,000.

create table employees(
employee_id INT PRIMARY KEY ,
first_name VARCHAR(50) ,
salary INT);
INSERT INTO employees(employee_id,first_name,salary)
VALUES
(100,'Steven',24000),
(101,'Neena',17000),
(102,'Lex',17000),
(103,'John',11000),
(104,'Robert',12000),
(105,'Leo',10000);

Query 1: Using a Numeric Value

Here, we want to retrieve the employee_id, first_name, and salary from the employees table whose salary is greater than 15000 then the query is:

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > 15000;

Output:

In this query

• Columns Selected: It retrieves employee_id, first_name, and salary from the employees table.
• Filter Condition: It only includes employees whose salary is greater than 15,000.

Query 2: Using a String Value

In this query, we provide the value '15000' as a string, and SQL automatically converts it to an integer to match the column data type.

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > '15000';

Output:

In this query:

• Retrieves employee_id, first_name, and salary from the employees table.
• Filters to include only employees with salary greater than 15,000.
• The string '15000' is automatically converted to a number for comparison.

Explicit Data-Type Conversion

Explicit data type conversion, or type casting, occurs when a value is explicitly converted from one data type to another. This is necessary when SQL cannot automatically determine the correct conversion, or when it is important to ensure the data is processed in a specific way.

SQL provides several functions for explicit type conversion, including:

• TO_CHAR(): Converts numbers or dates to a string.
• TO_NUMBER(): Converts a string to a numeric type.
• TO_DATE(): Converts a string to a date.

Example of Explicit Data Type Conversion

These conversion functions (TO_CHAR, TO_NUMBER, TO_DATE) ensure data is stored in one format but can be displayed, compared, or processed in another as per the requirement.

1. TO_CHAR Function

TO_CHAR function is used to typecast a numeric or date input to a character type with a format model (optional).

TO_CHAR(expression, 'format_model')

Using the TO_CHAR Function with Dates

TO_CHAR(date, ’format_model’)

The format model:

• Must be enclosed in single quotation marks and is case sensitive
• Can include any valid date format element in the query
• Has an fm element to remove padded blanks or suppress leading zeros
• Is separated from the date value by a comma

Example:

SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired
FROM employees
WHERE last_name = ’Higgins’;

Output :

Elements of the Date Format Model

Date Format Elements - Time Formats
Use the formats listed in the following tables to display time information and literals and to change numerals to spelled numbers.

Other Formats

Specifying Suffixes to Influence Number Display

Example :

SELECT last_name,
TO_CHAR(hire_date, ’fmDD Month YYYY’)
AS HIREDATE
FROM employees;

Output :

2. Using the TO_CHAR Function with Numbers

TO_CHAR(number, ’format_model’)

These are some of the format elements you can use with the TO_CHAR function to display a number value as a character :

Example :

SELECT TO_CHAR(salary, ’$99,999.00’) SALARY
FROM employees
WHERE last_name = ’Ernst’;

Output :

Using the TO_NUMBER and TO_DATE Functions :

Convert a character string to a number format using the TO_NUMBER function :

TO_NUMBER(char[, ’format_model’])

Convert a character string to a date format using the TO_DATE function:

TO_DATE(char[, ’format_model’])

These functions have an fx modifier. This modifier specifies the exact matching for the character argument and date format model of a TO_DATE function.
Example :

SELECT last_name, hire_date
FROM employees
WHERE hire_date = TO_DATE(’May 24, 1999’, ’fxMonth DD, YYYY’);

Output :