Conversion Function in SQL

In SQL data type conversion is important for effective database management and accurate query results. Data type conversion ensures that data from different sources or columns can be correctly interpreted and manipulated, especially when dealing with different formats like numbers, text, dates, and other data types.

This article provides an in-depth explanation, including examples of when and how they are used, as well as relevant SQL functions like TO_CHAR, TO_NUMBER, and TO_DATE.

Types of Data Type Conversion in SQL

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

• Implicit Data Type Conversion: This is done automatically by the database management system (DBMS) when SQL operations involve columns of different data types. For instance, a string value might automatically be converted into a numeric type if required by a mathematical operation.
  
• Explicit Data Type Conversion: This is done by the user, who specifies the conversion. This is necessary when SQL cannot automatically convert between data types, or when more control over the conversion 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);

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

Query:

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

Output:

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

Query:

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

Output:

Despite the second query using '15000' as a string, SQL automatically converts it into an integer before comparing it to the salary column, which is an integer type. The results for both queries will be the same.

Explicit Data-Type Conversion

Explicit data type conversion, or type casting, occurs when the user explicitly specifies the conversion of a value from one type to another. This is necessary when SQL cannot automatically infer the correct conversion or when you want to ensure the data is handled 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

Let's use the TO_CHAR function to convert a numeric value (salary) into a formatted string.

SELECT employee_id, TO_CHAR(salary, '$99,999.00') AS formatted_salary
FROM employees
WHERE last_name = 'Ernst';

In this query:

The TO_CHAR function is used to format the salary field as a string with a dollar sign and thousands separator.

Output:

SQL Functions for Explicit Data Type Conversion

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 :

Conclusion

Data type conversion in SQL, both implicit and explicit, is an important concept that helps you manipulate and present data effectively. Implicit conversion simplifies the process by automatically adjusting mismatched data types, while explicit conversion provides more control over how data is transformed. By using functions like TO_CHAR, TO_NUMBER, and TO_DATE, you can handle various types of data more efficiently and ensure the consistency of your database operations.