SQL - Conversion Functions

SQL - Data Type Conversion Functions

Data type conversion, or casting, is a fundamental operation in SQL. It’s used to explicitly convert a value from one data type to another. This is often necessary when comparing or joining columns with different data types, performing calculations, or formatting data for display.

Standard vs. Database-Specific Functions

The ANSI SQL standard provides the CAST function, which is the most portable and widely supported method for data type conversion. However, many database systems offer their own proprietary functions, like CONVERT in SQL Server, which may provide additional formatting capabilities.

Key Conversion Functions

Below is a comparison of the most common conversion functions and their typical use cases.

Function	Primary System	Description & Use Case
CAST(expression AS datatype)	ANSI Standard	The most portable way to convert types. Throws an error if the conversion is not possible. Use for converting strings to numbers, numbers to strings, or strings to dates.
CONVERT(datatype, expression, [style])	SQL Server	Similar to CAST, but includes an optional style parameter for formatting dates and numbers into specific string representations (e.g., ‘dd/mm/yyyy’).
TRY_CAST(expression AS datatype)	SQL Server, Azure SQL	A safe version of CAST. It attempts the conversion and returns NULL if it fails, instead of throwing an error. Ideal for cleaning messy or unreliable data.
TRY_CONVERT(datatype, expression, [style])	SQL Server, Azure SQL	The safe version of CONVERT. Returns NULL on conversion failure. Useful when formatting data that may not be valid.
expression::datatype	PostgreSQL	A concise, non-standard syntax specific to PostgreSQL for type casting. Functionally equivalent to CAST(expression AS datatype).

Example: Using CAST

Imagine a table where a price is incorrectly stored as text. To perform a calculation, you must cast it to a numeric type.

-- Storing numeric data as VARCHAR is a common anti-pattern
CREATE TABLE product_listings (product_name VARCHAR(100), price_text VARCHAR(20));
INSERT INTO product_listings VALUES ('Laptop', '1299.99'), ('Mouse', '25');

-- We must CAST price_text to a numeric type to calculate a 10% tax
SELECT
    product_name,
    price_text,
    CAST(price_text AS DECIMAL(10, 2)) * 1.10 AS price_with_tax
FROM product_listings;

Example: Safe Conversion with TRY_CAST

Now, consider what happens if the data is messy. Using CAST would cause the query to fail.

-- Adding a row with invalid data
INSERT INTO product_listings VALUES ('Keyboard', 'Not Available');

-- This query would FAIL with an error using CAST
-- SELECT CAST(price_text AS DECIMAL(10, 2)) FROM product_listings;

-- Using TRY_CAST (in SQL Server) handles the error gracefully
SELECT
    product_name,
    TRY_CAST(price_text AS DECIMAL(10, 2)) AS price_numeric
FROM product_listings;

-- Output would be:
-- Laptop, 1299.99
-- Mouse, 25.00
-- Keyboard, NULL

Best Practices

• Design with Correct Types: The best solution is to always use the correct data types in your table schema from the beginning. Avoid storing numbers or dates as strings.
• Prefer Standard CAST: For maximum portability across different database systems, use the standard CAST function unless you specifically need a feature from a proprietary function like CONVERT’s style codes.
• Use Safe Casting for Unreliable Data: When processing data from external sources or user input, use safe casting functions like TRY_CAST to prevent query failures and gracefully handle invalid data.