Types and CAST in SQL

A column's declared type is a promise about storage, not always a wall.

SELECT
    CAST('42' AS INTEGER) + 1 AS parsed,
    CAST(7 AS REAL) / 2 AS real_division,
    7 / 2 AS integer_division,
    TYPEOF(price) AS price_type,
    TYPEOF(NULL) AS missing
FROM products
LIMIT 1;

How it works

1. INTEGER, REAL, TEXT and BLOB are the storage classes underneath.
2. CAST converts explicitly; comparisons convert implicitly and surprise you.
3. TYPEOF reports what a value actually is right now.

Keywords and builtins used here

• AS
• CAST
• FROM
• INTEGER
• LIMIT
• NULL
• REAL
• SELECT

The run, in numbers

Lines

8

Characters to type

184

Tokens

47

Three-star pace

80 tpm

At the three-star pace of 80 tokens a minute, this run takes about 35 seconds.

Type this snippet

Step 3 of 8 in Tables & rows, step 16 of 23 in Language basics.

← Previous Next →