CAST Function - SQL

Overview

The CAST function in SQL converts an expression from one data type to another. It is useful for ensuring that values are in the desired format or data type for calculations and comparisons.

Example:

SELECT CAST(10 AS CHAR(2))

Syntax:

SELECT CAST(expression AS target_data_type)

expression is the value to convert from one data type to another.

target_data_type is the data type to which you want to convert the expression.

Sample Data:

    
            first_name
            vacation_days
        
            Frank
            5
        
            Jane
            2
        
            Ashley
            3
        
            Glenn
            -3
        
            Kelly
            2
        
            Richard
            -7
        
            George
            2
        
            Kyle
            1
        
            James
            -2
        
            Gustavo
            -10

first_name	vacation_days
Frank	5
Jane	2
Ashley	3
Glenn	-3
Kelly	2
Richard	-7
George	2
Kyle	1
James	-2
Gustavo	-10

List of target_data_type inputs

Character String Types:

CHAR(n) or CHARACTER(n) - Fixed-length character string.
VARCHAR(n) or CHARACTER VARYING(n) - Variable-length character string.
TEXT - Variable-length character string without a specified limit.

Numeric Types:

INT or INTEGER - Integer value.
SMALLINT - Small integer value.
BIGINT - Large integer value.
FLOAT - Single-precision floating-point number.
REAL - Single-precision floating-point number.
DOUBLE PRECISION - Double-precision floating-point number.
DECIMAL(p, s) or NUMERIC(p, s) - Exact numeric value with precision and scale.

Date and Time Types:

DATE - Calendar date.
TIME - Time of day.
TIMESTAMP - Date and time.
TIMESTAMP WITH TIME ZONE - Date and time with time zone.
TIMESTAMP WITHOUT TIME ZONE - Date and time without time zone.

Binary Types:

BINARY(n) - Fixed-length binary data.
VARBINARY(n) - Variable-length binary data.
BLOB - Large binary data object.

Boolean Types:

BOOLEAN - True or false value.

Other Types:

INTERVAL - Time interval.
UUID - Universally unique identifier.
ARRAY - Array of elements.
JSON or JSONB - JSON formatted data.

Example: Hard-coded value

In this example, we are converting the numeric value 10 into a character string of length 2 using the CAST function. The output is 10 as a string.

Example: Query without the WHERE Statement

In this example, we are converting the vacation_days column values into character strings with a length of 2 using the CAST function. The output is the vacation_days values as 2 character strings.

Example: Query with the WHERE Statement

In this example, we are filtering for only rows in department_id 1. We are converting the vacation_days column values into character strings with a length of 2 using the CAST function. The output is the vacation_days values as 2 character strings.