Data Conversion and Casting

Difficulty: Intermediate
Generated on: 2025-07-10 02:26:51
Category: SQL Cheatsheet for Database Development

SQL Data Conversion and Casting Cheatsheet (Intermediate)

1. Quick Overview

Data conversion and casting in SQL refers to the process of changing a value from one data type to another. This is essential for:

Data Compatibility: Ensuring data types match when comparing, joining, or inserting values.
Data Manipulation: Performing operations that require specific data types (e.g., arithmetic on numbers, string concatenation).
Data Presentation: Formatting data for display or reporting (e.g., date/time formatting, numeric precision).

Choosing the right data type and conversion method is crucial for data integrity and query performance.

2. Syntax

SQL provides several functions and operators for data conversion and casting. The specific syntax varies slightly depending on the database system.

ANSI SQL Standard:

CAST(expression AS data_type): The standard SQL way to convert data types.

Database-Specific Syntax:

MySQL:
- CONVERT(expression, data_type)
- CAST(expression AS data_type) (same as ANSI SQL)
PostgreSQL:
- CAST(expression AS data_type) (same as ANSI SQL)
- expression::data_type (PostgreSQL-specific shorthand)
SQL Server:
- CAST(expression AS data_type) (same as ANSI SQL)
- CONVERT(data_type, expression, style) (More flexible for date/time and numeric formatting)
Oracle:
- CAST(expression AS data_type) (same as ANSI SQL)
- TO_CHAR(expression, format) (For converting to strings with specific formats)
- TO_NUMBER(expression, format) (For converting to numbers with specific formats)
- TO_DATE(expression, format) (For converting to dates with specific formats)

Syntax Examples:

-- ANSI SQL CAST
SELECT CAST('123' AS INTEGER);

-- PostgreSQL shorthand
SELECT '456'::INTEGER;

-- SQL Server CONVERT with style
SELECT CONVERT(VARCHAR(10), GETDATE(), 101); -- MM/DD/YYYY

-- Oracle TO_CHAR with format
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

3. Common Use Cases

Converting Strings to Numbers: When numeric data is stored as strings (e.g., in CSV imports).
Converting Numbers to Strings: For display purposes or concatenation with other strings.
Converting Dates/Times to Strings: For specific date/time formats in reports.
Converting between Date/Time Types: (e.g., DATE to DATETIME or TIMESTAMP).
Handling Nullable Columns: Using COALESCE or ISNULL in conjunction with casting to handle NULL values.
Performing Arithmetic Operations on Strings: Converting strings to numbers before performing calculations.
Comparing Different Data Types: Casting one data type to match another before comparison.

4. Best Practices

Explicit Casting: Always use explicit casting (CAST or database-specific equivalents) rather than relying on implicit conversions. Implicit conversions can lead to unexpected behavior and data loss.
Data Type Selection: Choose the most appropriate data type for your data to minimize storage space and improve performance.
Error Handling: Use TRY_CAST (SQL Server) or similar functions to handle potential conversion errors gracefully.
Performance: Casting can be resource-intensive. Avoid unnecessary casting, especially in WHERE clauses. Optimize by pre-casting data during data loading or transformation if possible.
Data Validation: Validate data before casting to prevent errors and ensure data integrity.
Locale Awareness: When converting dates and numbers to strings, consider the locale settings to ensure correct formatting for different regions.
String Length: When converting to VARCHAR or other string types, specify an appropriate length to avoid truncation.

5. Examples

Sample Data:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    PriceString VARCHAR(10),  -- Price stored as string
    OrderDateString VARCHAR(20) -- Date stored as string
);

INSERT INTO Products (ProductID, ProductName, PriceString, OrderDateString) VALUES
(1, 'Laptop', '1200.50', '2023-10-26'),
(2, 'Mouse', '25', '2023-10-27 10:00:00'),
(3, 'Keyboard', '75.99', '2023-10-28'),
(4, 'Monitor', '350', '2023-10-29 14:30:00');

Example 1: Converting String to Decimal (MySQL, PostgreSQL, SQL Server)

-- Convert PriceString to DECIMAL for calculations
SELECT ProductID, ProductName, CAST(PriceString AS DECIMAL(10, 2)) AS Price
FROM Products;

--  ProductID | ProductName | Price
-- -----------+-------------+---------
--          1 | Laptop      | 1200.50
--          2 | Mouse       |   25.00
--          3 | Keyboard    |   75.99
--          4 | Monitor     |  350.00

Example 2: Converting String to Date (SQL Server, Oracle, PostgreSQL, MySQL)

-- SQL Server: Convert OrderDateString to DATETIME
SELECT ProductID, ProductName, CONVERT(DATETIME, OrderDateString) AS OrderDate
FROM Products;

-- PostgreSQL: Convert OrderDateString to TIMESTAMP
SELECT ProductID, ProductName, CAST(OrderDateString AS TIMESTAMP) AS OrderDate
FROM Products;

-- MySQL: Convert OrderDateString to DATETIME
SELECT ProductID, ProductName, STR_TO_DATE(OrderDateString, '%Y-%m-%d %H:%i:%s') AS OrderDate
FROM Products;

-- Oracle: Convert OrderDateString to DATE
SELECT ProductID, ProductName, TO_DATE(OrderDateString, 'YYYY-MM-DD HH24:MI:SS') AS OrderDate
FROM Products;

-- ProductID | ProductName | OrderDate
-- -----------+-------------+-----------------------
--          1 | Laptop      | 2023-10-26 00:00:00
--          2 | Mouse       | 2023-10-27 10:00:00
--          3 | Keyboard    | 2023-10-28 00:00:00
--          4 | Monitor     | 2023-10-29 14:30:00

Example 3: Using TRY_CAST to handle invalid numeric strings (SQL Server)

-- Add a row with an invalid price
INSERT INTO Products (ProductID, ProductName, PriceString, OrderDateString) VALUES
(5, 'Pen', 'abc', '2023-10-30');

-- Use TRY_CAST to handle potential conversion errors.  Returns NULL if conversion fails.
SELECT ProductID, ProductName, TRY_CAST(PriceString AS DECIMAL(10, 2)) AS Price
FROM Products;

-- ProductID | ProductName | Price
-- -----------+-------------+---------
--          1 | Laptop      | 1200.50
--          2 | Mouse       |   25.00
--          3 | Keyboard    |   75.99
--          4 | Monitor     |  350.00
--          5 | Pen         |    NULL   <--- Handles invalid data

Example 4: Converting Number to String with Formatting (SQL Server)

-- Convert Price to VARCHAR with currency formatting
SELECT ProductID, ProductName, FORMAT(CAST(PriceString AS DECIMAL(10, 2)), 'C', 'en-US') AS FormattedPrice
FROM Products;

-- ProductID | ProductName | FormattedPrice
-- -----------+-------------+----------------
--          1 | Laptop      | $1,200.50
--          2 | Mouse       | $25.00
--          3 | Keyboard    | $75.99
--          4 | Monitor     | $350.00
--          5 | Pen         | NULL

Example 5: Converting Date to String with Formatting (Oracle)

SELECT ProductID, ProductName, TO_CHAR(TO_DATE(OrderDateString, 'YYYY-MM-DD HH24:MI:SS'), 'DD-MON-YYYY') AS FormattedDate
FROM Products;

-- ProductID | ProductName | FormattedDate
-- -----------+-------------+---------------
--          1 | Laptop      | 26-OCT-2023
--          2 | Mouse       | 27-OCT-2023
--          3 | Keyboard    | 28-OCT-2023
--          4 | Monitor     | 29-OCT-2023
--          5 | Pen         | 30-OCT-2023

6. Common Pitfalls

Data Truncation: Converting to a data type with insufficient length (e.g., converting a long string to a VARCHAR(10)).
Invalid Data: Trying to convert a string that cannot be converted to the target data type (e.g., converting “abc” to an integer). Use TRY_CAST or validate data first.
Incorrect Date/Time Formats: Using the wrong format string when converting dates and times.
Loss of Precision: Converting a DECIMAL to an INT can truncate decimal places.
Implicit Conversions: Relying on implicit conversions can lead to unexpected results and performance problems. Always use explicit casting.
Locale Issues: Forgetting about locale-specific formatting when converting numbers and dates to strings.
Null Values: Failing to handle NULL values properly during conversion. Use COALESCE or ISNULL to provide default values.
Performance Bottlenecks: Excessive casting in WHERE clauses can significantly slow down queries. Consider indexing columns that are frequently cast.

Troubleshooting:

Conversion Errors: Check the data to ensure it is valid for the target data type. Use TRY_CAST or similar functions for error handling.
Incorrect Results: Verify that the data types are being converted correctly and that the format strings are accurate.
Performance Problems: Analyze query execution plans to identify performance bottlenecks caused by casting. Consider pre-casting data or optimizing query logic.

7. Database Variations

Feature	MySQL	PostgreSQL	SQL Server	Oracle
`CAST` Syntax	`CAST(expression AS data_type)`	`CAST(expression AS data_type)` or `expression::data_type`	`CAST(expression AS data_type)`	`CAST(expression AS data_type)`
`CONVERT` Syntax	`CONVERT(expression, data_type)`	N/A	`CONVERT(data_type, expression, style)`	N/A
Date/Time Conversion	`STR_TO_DATE(string, format)`	`TO_TIMESTAMP(string, format)`	`CONVERT(DATETIME, string, style)`	`TO_DATE(string, format)`
Number Conversion	`CAST(string AS DECIMAL)`	`CAST(string AS NUMERIC)`	`CAST(string AS DECIMAL)`	`TO_NUMBER(string, format)`
String Conversion	`CAST(number AS CHAR)`	`CAST(number AS TEXT)`	`CAST(number AS VARCHAR)`	`TO_CHAR(number, format)`
Error Handling	Check for errors after conversion	Check for errors after conversion	`TRY_CAST(expression AS data_type)`	Handle exceptions using PL/SQL blocks
Formatting	`FORMAT(number, decimals)`	`TO_CHAR(number, format)`	`FORMAT(number, format, locale)`	`TO_CHAR(number, format)`

This cheatsheet provides a practical guide to data conversion and casting in SQL. Remember to choose the appropriate data types and conversion methods for your specific needs, and always prioritize data integrity and query performance. Always test your conversions thoroughly to ensure accuracy and prevent unexpected behavior.