Conversion Function in SQL

Data type conversion in SQL allows data to be transformed between formats (numbers, text, dates) to ensure accurate query results. Common functions include TO_CHAR, TO_NUMBER, and TO_DATE for handling these conversions efficiently.

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

First, we create a demo SQL database and table, on which we can demonstrate the Implicit Data Type Conversion.

Query 1: Using a Numeric Value

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

Output:

• Retrieves employee_id, first_name, and salary from the employees table.
• Filters and shows only records where salary is greater than 15000.

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:

• 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 (type casting) is the manual conversion of a value from one data type to another. It is used when automatic conversion isn’t possible or when precise control over how data is processed is required.

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. Consider this table for the examples below:

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

Query:

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

Output :

• Explicitly converts the hire_date from DATE to TEXT using TO_CHAR().
• Displays only records where last_name = 'Higgins'.

Elements of the Date Format Model

They are format codes (like DD, MM, YYYY) used to control how date parts such as day, month, and year are displayed or interpreted in SQL.

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

Some other formats are shown below:

Specifying Suffixes to Influence Number Display

These suffixes control how numbers are displayed, such as showing ordinal or spelled-out formats.

Query:

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

Output :

• Converts hire_date into a readable text format like “25 January 2005” using explicit type conversion.
• Displays each employee’s last name with the formatted hire date.

2. Using the TO_CHAR Function with Numbers

The TO_CHAR() function is used to convert numeric values into formatted text, allowing control over number display such as decimals, commas, and currency symbols.

Syntax:

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 :

Query:

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

Output:

• Formats salary with currency symbols using TO_CHAR().
• Displays result only for employee with last name 'Clark'.

3. 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.

Query:

SELECT last_name, hire_date
FROM employees
WHERE hire_date = TO_DATE('June 20, 2004', 'fxMonth DD, YYYY');

Output:

• TO_NUMBER() converts a text (character) value into a numeric format.
• TO_DATE() converts a text value into a date using a specified format, and the fx modifier enforces an exact date format match.

Suggested Quiz

Edit Quiz

6 Questions

What is implicit data type conversion in SQL?

• A

  Manual conversion by the user

• B

  Automatic conversion done by SQL

• C

  Conversion done only with functions

• D

  Conversion requiring strict format models

Explanation:

Implicit conversion happens automatically when SQL converts one type to another based on context.

Which function converts a DATE or NUMBER into a formatted text value?

• A

  TO_NUMBER()

• B

  TO_DATE()

• C

  CAST()

• D

  TO_CHAR()

Explanation:

TO_CHAR() explicitly converts numeric or date values into formatted character strings.

What does this query do?
WHERE salary > '15000'

• A

  Produces an error

• B

  Compares text alphabetically

• C

  SQL converts '15000' to a number automatically

• D

  Converts salary to a string

Explanation:

SQL performs implicit conversion, changing the string '15000' into a numeric value.

Which function converts text into a date using a format model?

• A

  TO_CHAR()

• B

  TO_DATE()

• C

  STR_TO_DATE()

• D

  DATE_FORMAT()

Explanation:

TO_DATE() converts strings into date values in the required date format.

What does the fx modifier in TO_DATE() enforce?

• A

  Enforces exact character–format matching

• B

  Automatically detects date format

• C

  B. Ignores whitespace and punctuation

• D

  Converts numbers to spelled-out words

Explanation:

The fx modifier requires strict matching between the input string and the format model.

Which function would you use to format salary like $12,500.00?

• A

  TO_DATE()

• B

  TO_NUMBER()

• C

  TO_CHAR()

• D

  FORMAT()

Explanation:

TO_CHAR(number, 'format') allows currency signs, commas, and decimal formatting.

Quiz Completed Successfully

Your Score :   2/6

Accuracy :  0%

Login to View Explanation

1/6 1/6 < Previous Next >