CAST / CONVERT

-- Converts a value to the specified type (standard SQL).
SELECT CAST(value AS type);

-- Converts the character encoding (primary use of MySQL's CONVERT).
SELECT CONVERT(value USING charset);

-- Also performs type conversion (MySQL-specific extension).
SELECT CONVERT(value, type);

Syntax	Description
CAST(value AS SIGNED)	Converts a value to a signed integer (MySQL).
CAST(value AS UNSIGNED)	Converts a value to an unsigned integer (MySQL).
CAST(value AS DECIMAL(p,s))	Converts a value to a fixed-point number with precision p and s decimal places.
CAST(value AS CHAR)	Converts a value to a string.
CAST(value AS DATE)	Converts a value to a date type.
CAST(value AS DATETIME)	Converts a value to a datetime type.
CAST(value AS INTEGER)	Converts a value to an integer (standard SQL / PostgreSQL).

-- Converts a numeric string to an integer and performs arithmetic.
SELECT CAST('12345' AS SIGNED) + 100 AS result;

-- Converts an integer to a string and concatenates it.
SELECT CONCAT('Order number: ', CAST(order_id AS CHAR)) AS label
FROM orders;

-- Converts a string to a date type for comparison.
SELECT order_id, order_date
FROM orders
WHERE order_date >= CAST('2025-01-01' AS DATE);

-- Formats a price to two decimal places.
SELECT product_name,
       CAST(price AS DECIMAL(10, 2)) AS price_decimal
FROM products;

-- Sums a column of numeric strings by casting them first.
SELECT SUM(CAST(amount_str AS DECIMAL(12, 2))) AS total
FROM sales_import;

-- Result of: SELECT CAST('12345' AS SIGNED) + 100 AS result;
-- +--------+
-- | result |
-- +--------+
-- |  12445 |
-- +--------+

-- Result of: SELECT product_name, CAST(price AS DECIMAL(10, 2)) AS price_decimal FROM products;
-- +--------------+---------------+
-- | product_name | price_decimal |
-- +--------------+---------------+
-- | Laptop       |      89800.00 |
-- | Mouse        |       2980.00 |
-- | Keyboard     |       5500.00 |
-- +--------------+---------------+

CAST is supported across MySQL, PostgreSQL, Oracle, SQL Server, and SQLite as standard SQL. However, the type names you can specify differ between databases.

-- MySQL: SIGNED / UNSIGNED are available.
SELECT CAST('12345' AS SIGNED) + 100 AS result;

-- PostgreSQL: Use INTEGER / NUMERIC / TEXT, etc.
SELECT CAST('12345' AS INTEGER) + 100 AS result;

-- SQL Server: Use INT / DECIMAL / VARCHAR, etc.
SELECT CAST('12345' AS INT) + 100 AS result;

In PostgreSQL, you can use the :: operator instead of CAST to convert types.

-- PostgreSQL: Convert types using the :: operator.
SELECT '12345'::INTEGER + 100 AS result;
SELECT order_date::TEXT FROM orders;

SQL Server's CONVERT has a different syntax from MySQL's and supports a style code for formatting dates.

-- SQL Server: Arguments are in the order CONVERT(type, value, style).
SELECT CONVERT(VARCHAR, order_date, 111) AS formatted_date FROM orders;
-- Example output: 2025/01/15

In Oracle, dedicated conversion functions such as TO_NUMBER, TO_CHAR, and TO_DATE are commonly used.

-- Oracle: Use dedicated conversion functions.
SELECT TO_NUMBER('12345') + 100 AS result FROM DUAL;
SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS formatted_date FROM orders;

CAST is defined in the SQL standard and is available in both MySQL and PostgreSQL. Type conversion is often needed when data imported from an external source has numbers stored as strings, or when formats differ between systems.

MySQL's CONVERT(value, type) is a MySQL-specific syntax that performs the same type conversion as CAST. In contrast, CONVERT(value USING utf8mb4) is a syntax for converting character encodings and serves a different purpose.

To format a date into a specific pattern, see EXTRACT / FORMAT. To handle or replace NULL values, see COALESCE / NULLIF.