Basic CAST Usage
Convert data types effectively using the CAST function. For more tips, explore our AI SQL Tools for automatic query optimization.
Simple Type Conversions
SELECT CAST('123' AS INT) as converted_number;
SELECT CAST(123 AS VARCHAR) as converted_string;
SELECT CAST('2024-01-15' AS DATE) as
Database-Specific Syntax
SQL Server
SELECT CAST(price AS DECIMAL(10,2)) as formatted_price
FROM
MySQL
SELECT CAST(price AS DECIMAL(10,2)) as formatted_price
FROM
PostgreSQL
SELECT CAST(price AS NUMERIC(10,2)) as formatted_price
FROM
For more examples, check out SQL Query Optimization Techniques to make your database faster.
Common Use Cases
1. Numeric Formatting
SELECT
product_name,
CAST(price AS DECIMAL(10,2)) as formatted_price
FROM
2. Date Manipulations
SELECT
order_date,
CAST(EXTRACT(YEAR FROM order_date) AS VARCHAR) as order_year
FROM
3. String Operations
SELECT
'Order #' + CAST(order_id AS VARCHAR) as order_reference
FROM
Advanced Applications
Dynamic SQL
DECLARE @sql_query NVARCHAR(MAX);
DECLARE @table_name VARCHAR(50) = 'orders';
SET @sql_query = '
SELECT *
FROM ' + @table_name + '
WHERE order_date >= CAST(DATEADD(day, -30, GETDATE()) AS DATE)';
EXEC
Learn more about SQL AI Solutions for building dynamic queries efficiently.
Performance Optimization
Index Usage
ALTER TABLE orders
ADD order_date_only AS CAST(order_datetime AS DATE) PERSISTED;
CREATE INDEX idx_order_date
ON orders(order_date_only)
Batch Processing
UPDATE sales_import
SET
amount = CAST(amount_string AS DECIMAL(10,2)),
transaction_date = CAST(date_string AS DATE)
WHERE
TRY_CAST(amount_string AS DECIMAL(10,2)) IS NOT NULL
Best Practices
1. Error Handling
SELECT
TRY_CAST(value AS INT) as safe_integer,
CASE
WHEN TRY_CAST(value AS INT) IS NULL
THEN 'Invalid Number'
ELSE 'Valid Number'
END as validation_result
FROM
2. Data Validation
SELECT
column_value,
CASE
WHEN REGEXP_LIKE(column_value, '^[0-9]+$')
THEN CAST(column_value AS INT)
ELSE NULL
END as converted_value
FROM
3. Performance Considerations
CREATE TABLE performance_example (
id INT,
price DECIMAL(10,2),
description VARCHAR(100)
);
CREATE INDEX idx_price ON performance_example(price)
FAQs
Q: When should I use CAST vs CONVERT?
A: CAST is ANSI standard and works across databases. Use CONVERT for database-specific features.
Q: Can CAST affect performance?
A: Yes, especially in WHERE clauses. Create computed columns for frequent conversions.
Need help? Discover more about AI SQL Tools to streamline SQL queries and improve performance.
For complex query optimizations, visit our SQL Optimization Guide.
