CAST / TRY_CAST / CONVERT

Category: Conversion Functions

Platform Support:

✅ Snowflake | ✅ BigQuery | ✅ Databricks

Description

Type conversion functions transform data from one type to another. CAST is standard SQL for type conversion. TRY_CAST attempts conversion and returns NULL on failure instead of error. CONVERT is an alternative syntax. Essential for data type compatibility and transformations.

Syntax

CAST (Standard SQL):

CAST(expression AS data_type)

TRY_CAST (Safe conversion):

TRY_CAST(expression AS data_type)
-- Returns NULL if conversion fails

CONVERT (Alternative syntax):

-- SQL Server / Snowflake style
CONVERT(data_type, expression)

:: Operator (Snowflake shorthand):

expression::data_type

Platform-Specific Notes

Snowflake:

• CAST and TRY_CAST supported
• :: operator as shorthand
• TO_VARCHAR, TO_NUMBER, TO_DATE functions
• Flexible type coercion

BigQuery:

• CAST supported (primary method)
• SAFE_CAST (equivalent to TRY_CAST)
• No CONVERT function
• Strict type checking

Databricks:

• CAST and TRY_CAST supported
• :: operator supported
• Compatible with Spark SQL
• TO_TIMESTAMP, TO_DATE functions

Example 1: CAST - Basic Type Conversions

All Platforms:

SELECT 
    -- String to number
    CAST('12345' AS INTEGER) as string_to_int,
    CAST('123.45' AS DECIMAL(10,2)) as string_to_decimal,
    
    -- Number to string
    CAST(12345 AS VARCHAR(10)) as int_to_string,
    CAST(123.45 AS VARCHAR(20)) as decimal_to_string,
    
    -- String to date/timestamp
    CAST('2024-01-15' AS DATE) as string_to_date,
    CAST('2024-01-15 10:30:00' AS TIMESTAMP) as string_to_timestamp,
    
    -- Number to boolean
    CAST(1 AS BOOLEAN) as one_to_true,
    CAST(0 AS BOOLEAN) as zero_to_false;

Result:

Example 2: TRY_CAST - Safe Conversions

Snowflake/Databricks:

SELECT 
    order_id,
    discount_code,
    -- CAST would fail on invalid numbers
    TRY_CAST(discount_code AS INTEGER) as discount_value,
    -- Can use with COALESCE for defaults
    COALESCE(TRY_CAST(discount_code AS INTEGER), 0) as discount_or_zero
FROM orders;

BigQuery:

SELECT 
    order_id,
    discount_code,
    SAFE_CAST(discount_code AS INT64) as discount_value,
    COALESCE(SAFE_CAST(discount_code AS INT64), 0) as discount_or_zero
FROM orders;

Sample Data (orders table):

Result:

Example 3: Platform-Specific Conversion Functions

Snowflake:

SELECT 
    -- Using :: operator (Snowflake shorthand)
    '12345'::INTEGER as shorthand_cast,
    123.45::VARCHAR as number_to_string,
    
    -- Specific conversion functions
    TO_NUMBER('12345') as to_number,
    TO_VARCHAR(12345) as to_varchar,
    TO_DATE('2024-01-15', 'YYYY-MM-DD') as to_date,
    TO_TIMESTAMP('2024-01-15 10:30:00') as to_timestamp;

BigQuery:

SELECT 
    -- Must use CAST
    CAST('12345' AS INT64) as string_to_int,
    CAST(123.45 AS STRING) as number_to_string,
    
    -- Format-aware functions
    PARSE_DATE('%Y-%m-%d', '2024-01-15') as parse_date,
    PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2024-01-15 10:30:00') as parse_timestamp,
    FORMAT_DATE('%B %d, %Y', DATE '2024-01-15') as format_date;

Databricks:

SELECT 
    -- Using :: operator
    '12345'::INT as shorthand_cast,
    123.45::STRING as number_to_string,
    
    -- Specific functions
    TO_DATE('2024-01-15', 'yyyy-MM-dd') as to_date,
    TO_TIMESTAMP('2024-01-15 10:30:00') as to_timestamp;

Example 4: Data Cleaning with CAST

All Platforms:

SELECT 
    customer_id,
    -- Clean and convert price strings
    TRY_CAST(REPLACE(REPLACE(price_str, '$', ''), ',', '') AS DECIMAL(10,2)) as clean_price,
    
    -- Extract and convert year
    CAST(SUBSTRING(date_str, 1, 4) AS INTEGER) as year,
    
    -- Convert string boolean
    CASE 
        WHEN LOWER(TRIM(active_str)) IN ('true', 'yes', '1', 't', 'y') THEN CAST(1 AS BOOLEAN)
        WHEN LOWER(TRIM(active_str)) IN ('false', 'no', '0', 'f', 'n') THEN CAST(0 AS BOOLEAN)
        ELSE NULL
    END as is_active
FROM messy_data;

Sample Data (messy_data table):

Result:

Example 5: JSON to Structured Data

Snowflake:

SELECT 
    json_data:user_id::INTEGER as user_id,
    json_data:name::VARCHAR as name,
    json_data:age::INTEGER as age,
    TRY_CAST(json_data:salary AS DECIMAL(10,2)) as salary,
    json_data:active::BOOLEAN as is_active
FROM user_json_data;

BigQuery:

SELECT 
    CAST(JSON_EXTRACT_SCALAR(json_data, '$.user_id') AS INT64) as user_id,
    JSON_EXTRACT_SCALAR(json_data, '$.name') as name,
    SAFE_CAST(JSON_EXTRACT_SCALAR(json_data, '$.age') AS INT64) as age,
    SAFE_CAST(JSON_EXTRACT_SCALAR(json_data, '$.salary') AS NUMERIC) as salary,
    CAST(JSON_EXTRACT_SCALAR(json_data, '$.active') AS BOOL) as is_active
FROM user_json_data;

Example 6: Implicit vs Explicit Casting

All Platforms:

-- Implicit casting (database does automatically)
SELECT 
    '10' + 5 as implicit_string_to_number,  -- Result: 15
    10 || ' items' as implicit_number_to_string;  -- Result: '10 items'

-- Explicit casting (recommended for clarity)
SELECT 
    CAST('10' AS INTEGER) + 5 as explicit_conversion,
    CAST(10 AS VARCHAR) || ' items' as explicit_concat;

Common Data Type Conversions

Error Handling

CAST (throws error on failure):

-- ❌ This will ERROR
SELECT CAST('abc' AS INTEGER);
-- Error: Numeric value 'abc' is not recognized

TRY_CAST/SAFE_CAST (returns NULL on failure):

-- ✅ This returns NULL
SELECT TRY_CAST('abc' AS INTEGER) as result;
-- Result: NULL

-- Can handle with COALESCE
SELECT COALESCE(TRY_CAST('abc' AS INTEGER), -1) as result;
-- Result: -1

Performance Considerations

1. Explicit is better: Clear type conversions improve query optimization
2. Index impact: Casting indexed columns prevents index usage
3. Bulk operations: TRY_CAST safer for large datasets with unknown data quality
4. Format specifications: Platform-specific functions (TO_DATE) can be faster

Avoid casting in WHERE clauses:

-- ❌ Slow: Prevents index use
WHERE CAST(id AS VARCHAR) = '12345'

-- ✅ Fast: Uses index
WHERE id = 12345

Common Use Cases

1. Data import: Convert string data to appropriate types
2. API integration: Transform JSON to structured data
3. Data cleaning: Handle messy/inconsistent data
4. Type compatibility: Join tables with different column types
5. Display formatting: Convert for presentation
6. Calculations: Ensure numeric types for math operations

Best Practices

1. Use TRY_CAST for uncertain data: Prevents errors in production
2. Be explicit: Don't rely on implicit conversions
3. Validate first: Check data quality before casting
4. Handle NULLs: Use COALESCE with TRY_CAST
5. Choose appropriate precision: DECIMAL(10,2) vs DECIMAL(18,6)
6. Document conversions: Explain why casting is needed
7. Test edge cases: NULL, empty string, invalid formats
8. Use platform-specific functions: Often more feature-rich