---
layout: default
title: CAST Operations
parent: SQL Features
nav_order: 7
---
# CAST Operations
This document explains type casting in Oxibase based on the implementation and test files.
## Overview
Oxibase supports both explicit type casting using the CAST function and implicit type conversion in certain contexts. Type casting allows you to convert a value from one data type to another.
## Supported Data Types
Oxibase supports casting between these data types:
- `INTEGER` or `INT`: 64-bit signed integers
- `FLOAT`: 64-bit floating-point numbers
- `TEXT`, `STRING`, `VARCHAR`, or `CHAR`: Text strings
- `BOOLEAN` or `BOOL`: Boolean values
- `TIMESTAMP`, `DATETIME`, `DATE`, or `TIME`: Date and time values
- `JSON`: JSON data format
## Explicit CAST Syntax
To explicitly cast a value, use the CAST function:
```sql
CAST(value AS type)
```
Where:
- `value` can be a column reference, literal, or expression
- `type` is one of the supported data types
## Examples
### Basic CAST Operations
```sql
-- String to integer
SELECT CAST('123' AS INTEGER); -- Returns 123
-- Integer to string
SELECT CAST(42 AS TEXT); -- Returns '42'
-- Float to integer (truncates)
SELECT CAST(123.45 AS INTEGER); -- Returns 123
-- Boolean to integer
SELECT CAST(TRUE AS INTEGER); -- Returns 1
SELECT CAST(FALSE AS INTEGER); -- Returns 0
-- String to boolean
SELECT CAST('true' AS BOOLEAN); -- Returns TRUE
SELECT CAST('yes' AS BOOLEAN); -- Returns TRUE
SELECT CAST('false' AS BOOLEAN); -- Returns FALSE
SELECT CAST('0' AS BOOLEAN); -- Returns FALSE
-- Number to boolean
SELECT CAST(0 AS BOOLEAN); -- Returns FALSE
SELECT CAST(1 AS BOOLEAN); -- Returns TRUE
SELECT CAST(42 AS BOOLEAN); -- Returns TRUE
-- Integer to float
SELECT CAST(123 AS FLOAT); -- Returns 123.0
-- String to timestamp
SELECT CAST('2023-05-15 14:30:00' AS TIMESTAMP); -- Returns timestamp
-- Timestamp to string
SELECT CAST(NOW() AS TEXT); -- Returns formatted timestamp
```
### Using CAST in WHERE Clauses
```sql
-- Create a test table with mixed data types
CREATE TABLE cast_test (
id INTEGER PRIMARY KEY,
text_val TEXT,
int_val INTEGER,
float_val FLOAT,
bool_val BOOLEAN
);
-- Insert test data
INSERT INTO cast_test VALUES
(1, '100', 100, 100.5, TRUE),
(2, '200', 200, 200.5, FALSE),
(3, 'abc', 300, 300.5, TRUE);
-- Cast text column to integer in WHERE clause
SELECT id FROM cast_test WHERE CAST(text_val AS INTEGER) > 150; -- Returns 2
```
### Complex CAST Operations
```sql
-- Nested CAST operations
SELECT CAST(CAST(123.45 AS INTEGER) AS TEXT); -- Returns '123'
-- CAST with expressions
SELECT CAST(int_val * 2 AS TEXT) FROM cast_test;
-- CAST with NULL values
SELECT CAST(NULL AS INTEGER); -- Returns 0
SELECT CAST(NULL AS TEXT); -- Returns '' (empty string)
SELECT CAST(NULL AS BOOLEAN); -- Returns FALSE
```
## Type Conversion Rules
### To INTEGER
- **From FLOAT**: Truncates decimal portion (123.45 → 123)
- **From TEXT**: Parses numeric string ("123" → 123), non-numeric strings become 0
- **From BOOLEAN**: TRUE → 1, FALSE → 0
- **From TIMESTAMP**: Converts to Unix timestamp (seconds since epoch)
- **From NULL**: Returns 0
### To FLOAT
- **From INTEGER**: Direct conversion (123 → 123.0)
- **From TEXT**: Parses numeric string ("123.45" → 123.45), non-numeric strings become 0.0
- **From BOOLEAN**: TRUE → 1.0, FALSE → 0.0
- **From TIMESTAMP**: Converts to Unix timestamp with fractional seconds
- **From NULL**: Returns 0.0
### To TEXT
- **From INTEGER**: String representation (123 → "123")
- **From FLOAT**: String representation (123.45 → "123.45")
- **From BOOLEAN**: "true" or "false"
- **From TIMESTAMP**: ISO 8601 format ("2023-05-15T14:30:00Z")
- **From NULL**: Returns empty string ("")
### To BOOLEAN
- **From INTEGER**: 0 → FALSE, non-zero → TRUE
- **From FLOAT**: 0.0 → FALSE, non-zero → TRUE
- **From TEXT**: "true", "t", "yes", "y", "1" → TRUE (case-insensitive)
"false", "f", "no", "n", "0", "" → FALSE (case-insensitive)
- **From NULL**: Returns FALSE
### To TIMESTAMP
- **From INTEGER**: Interpreted as Unix timestamp
- **From FLOAT**: Interpreted as Unix timestamp with fractional seconds
- **From TEXT**: Parses date/time string in various formats
- **From NULL**: Returns zero time value
### To JSON
- **From TEXT**: Validated as JSON string
- **From other types**: Converted to JSON representation
## Implicit Type Conversion
Oxibase performs implicit type conversion in these contexts:
1. **Arithmetic operations**: When mixing numeric types
```sql
SELECT 1 + 2.5; ```
2. **Comparison operations**: When comparing different types
```sql
SELECT * FROM table WHERE id = '100'; ```
3. **Function arguments**: When a function expects specific types
```sql
SELECT ABS('-123'); ```
## NULL Handling in CAST Operations
As shown in the test files, casting NULL values follows these rules:
- NULL cast to INTEGER becomes 0
- NULL cast to FLOAT becomes 0.0
- NULL cast to TEXT becomes empty string ("")
- NULL cast to BOOLEAN becomes FALSE
- NULL cast to TIMESTAMP becomes zero time
## Best Practices
1. **Use explicit casts** for clarity when type conversion is intentional
2. **Be aware of data loss** when casting from higher precision to lower precision types (FLOAT to INTEGER loses decimal portion)
3. **Validate inputs** when casting from TEXT to ensure they're in the expected format
4. **Consider NULL handling** when casting columns that might contain NULL values
## Implementation Details
Oxibase implements CAST operations in various components:
- The parser (`src/parser/`) recognizes CAST expressions
- The scalar function implementation (`src/functions/scalar/cast.rs`) handles the CAST logic
- Type-specific cast expressions (`src/storage/expression/cast_expression.rs`) optimize CAST operations in storage layer
Parallel execution is applied where possible to improve CAST performance for large result sets.