Strings, Numbers, Booleans and Implicit Type Conversion

The data we'll use — the typed_demo table

When you create a column you declare a type like INTEGER / TEXT / REAL, and that decides how the column treats the values it holds.

In this article you'll walk through how strings, numbers, and booleans are stored and compared in SQL, and how the way this course's console handles types (also known as type affinity in the SQLite docs) differs from the strict types in MySQL.

Before jumping into exercises, take a quick look at the column definitions and sample rows of the typed_demo table. (Run it correctly and the explanation will appear.)

① Use PRAGMA table_info(typed_demo); to check the column names, declared types, and primary key.

② Use SELECT * FROM typed_demo; to preview every row.

Declared type vs storage class — every value carries its own type

The type a value actually has at runtime is called its storage class, and there are five: NULL / INTEGER (whole numbers) / REAL (decimals) / TEXT (strings) / BLOB (binary).

You can check what storage class a value currently has with the typeof(value) function.

If you put a numeric-looking string into a column declared TEXT, the column's policy is "treat this as text," so it stays a string.

But if you put a numeric-looking string like '123' into a column declared INTEGER, the column's policy kicks in and converts it to the integer 123 before storing it.

-- Quick standalone example showing "the column's declared type decides the stored type"
-- A numeric-looking string in an INTEGER column gets converted to an integer
CREATE TABLE IF NOT EXISTS aff_probe(num INTEGER, txt TEXT);
INSERT OR IGNORE INTO aff_probe VALUES ('123','123'), ('hello','hello');

-- Use typeof to inspect the actual storage class
SELECT num, typeof(num) AS num_class,
       txt, typeof(txt) AS txt_class
FROM aff_probe;
-- num column: '123' becomes an integer, 'hello' can't be converted so it stays text
-- txt column: both stay as text

Imagine you want to check "what type (integer / real / string) each value in typed_demo is actually stored as."

① Pull the a, b, c, and flag columns from typed_demo.

② Add a typeof() for each column too, aliased as a_class / b_class / c_class / flag_class.

③ Don't filter the rows — both rows should appear in the result.

Strings and numbers switch back and forth — '123' and 123

When a number is needed, a string is converted to a number automatically; when a string is needed, a number is converted to a string.

For example, '123' + 0 converts the string '123' to the number 123 and returns 123, so numeric-looking strings can be dropped straight into arithmetic.

Going the other way, 123 || '' (|| is string concatenation) turns the number into a string.

Comparison operators work the same way: if one side is an INTEGER column, the other side gets aligned as a number; if one side is a TEXT column, the other side gets aligned as a string.

But with two raw literals like '1' = 1 there's no column to set the policy, so the values stay different types and the result is 0 (no match).

-- Watch string <-> number conversion (read-only example)
SELECT '123' + 0     AS str_to_num,   -- string becomes number 123
       'abc' + 0     AS not_a_num,    -- can't convert, becomes 0
       123 || ''     AS num_to_str,   -- number becomes string '123'
       typeof('123' + 0) AS class_after_add,
       '1' = 1       AS literal_cmp,  -- raw literals: 0 (no match)
       7 / 2         AS int_div,      -- integer / integer = integer 3
       7.0 / 2       AS real_div;     -- a decimal in the mix gives 3.5

Imagine you want to "treat the numeric-looking strings in typed_demo's b column (TEXT) as numbers for arithmetic and comparison."

① Pull the b column from typed_demo.

② Add b + 0 aliased as b_plus0. Numeric-looking rows become numbers, and non-numeric rows become 0.

③ Then add a check for whether b as a number is greater than 5, aliased as gt5 (since you're comparing a b column value against a numeric literal, the comparison happens between numbers).

Booleans — TRUE / FALSE are stored as 1 / 0

SQL doesn't have a standalone boolean type — truth values are represented by the integers 1 (true) and 0 (false).

Comparison expressions like 1 = 1 or 3 > 2 return 1 for true and 0 for false.

Anywhere you can write a condition, like CASE WHEN column THEN ..., anything that isn't 0 (typically 1) is treated as true, and 0 is treated as false.

NULL is a third state that's neither true nor false ("unknown"), and in WHERE or CASE conditions it behaves like false in the sense that the row is "not picked."

-- Comparison expressions return 1 / 0 (read-only example)
SELECT (1 = 1)  AS is_true,    -- 1
       (1 = 2)  AS is_false,   -- 0
       (3 > 2)  AS gt,         -- 1
       TRUE     AS true_kw,    -- same as 1
       FALSE    AS false_kw;   -- same as 0

-- Use a flag column directly as a condition
SELECT a, flag,
  CASE WHEN flag THEN 'enabled' ELSE 'disabled' END AS state
FROM typed_demo;

Imagine you want to "turn the flag column (integer 1 / 0) in typed_demo into a human-readable label."

① Pull a and flag from typed_demo.

② Use flag directly as a condition in a CASE WHEN ... and add a third column aliased as state that returns 'enabled' for true (1) rows and 'disabled' for false (0) rows.

③ To also see what a comparison expression returns for true / false, add a fourth column with the result of flag = 1 aliased as is_on.

Compared with MySQL's strict types

What you've seen so far is implicit type conversion — values get reshaped flexibly based on context.

In contrast, MySQL and Oracle have strict types: when you declare CHAR(10) / VARCHAR(255) / TEXT for strings or INT / DECIMAL(10,2) for numbers, only values of that type go in, and length and precision are enforced exactly as declared.

A number usually won't slip into a string column via implicit conversion, and values that exceed the declared length will either error out or get truncated.

The code below is the MySQL way of declaring types.

This course's console doesn't enforce length checks by design, so when you study length constraints like VARCHAR(10), just remember "this is how MySQL writes it" — that syntax carries over when you move to other databases.

-- Strict type declarations in MySQL (read-only — don't run in this console)
CREATE TABLE product (
  id        INT            PRIMARY KEY,   -- integers only
  code      CHAR(8),                      -- fixed length, 8 chars
  name      VARCHAR(100)   NOT NULL,      -- up to 100 chars
  note      TEXT,                         -- long string
  price     DECIMAL(10,2)  NOT NULL,      -- 8 digits + 2 decimals
  in_stock  TINYINT(1)     DEFAULT 0      -- 0/1 flag for booleans
);

-- In MySQL, putting 101 chars into VARCHAR(100) will
-- either error out or get truncated depending on settings.
-- Putting 'abc' into an INT column will error out.
-- This course's console runs all of it thanks to implicit type conversion.

Tips — CHAR vs VARCHAR and when to use each

`CHAR(n)` is fixed-length: if the value is shorter than n characters, it's padded with trailing spaces to fill all n characters.

`VARCHAR(n)` is variable-length: it keeps the value at its actual length (n is the upper bound).

Reach for `CHAR` when: the value is always a fixed length, like a country code ('JP' / 'US'), a gender code, or a fixed-length section of a hash. The fixed layout can be marginally faster for comparisons and record-position math.

Reach for `VARCHAR` when: lengths vary — names, email addresses, titles, descriptions, and so on. It only takes up the space it actually needs, so storage stays smaller.

When in doubt, `VARCHAR` is the safer default. Using CHAR for variable-length data can cause subtle comparison mismatches because of the trailing-space padding (e.g. 'JP' vs 'JP ' being handled inconsistently).

Imagine you want to "sum and average the values in typed_demo's b column (TEXT) as numbers, and count how many rows are true in the flag column." This shows that implicit type conversion lets you aggregate text columns numerically.

① Pull the sum of typed_demo's b column aliased as sum_b, and the average aliased as avg_b. Only numeric-looking rows get added as numbers.

② In the same query, pull the sum of the flag column aliased as enabled_count (since flag is 1 / 0, the sum equals the count of true rows).

③ Don't group by anything — return a single summary row for the whole table.