Character Types

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- CHAR type declarations and behavior
CREATE TABLE country_codes (
    country_code    CHAR(2) PRIMARY KEY,      -- 'US', 'GB', 'JP'
    country_name    VARCHAR(100) NOT NULL
);
 
CREATE TABLE currencies (
    currency_code   CHAR(3) PRIMARY KEY,      -- 'USD', 'EUR', 'GBP'
    currency_name   VARCHAR(50) NOT NULL,
    symbol          CHAR(1)                   -- '$', '€', '£'
);
 
-- Fixed-format identifiers
CREATE TABLE products (
    -- SKU format: 3 letters + 5 digits + 2 letters (e.g., 'ABC12345XY')
    sku             CHAR(10) PRIMARY KEY,
    product_name    VARCHAR(200) NOT NULL
);
 
-- Demonstrating padding behavior
INSERT INTO country_codes (country_code, country_name)
VALUES ('US', 'United States');
 
-- The stored value is 'US' (no padding needed - exact length)
 
INSERT INTO country_codes (country_code, country_name)
VALUES ('A', 'Test');  -- Stored as 'A ' (right-padded with space)
 
-- Comparison behavior varies by database!
-- Standard SQL: 'A' = 'A ' returns TRUE (spaces ignored)
-- Some configurations: 'A' = 'A ' returns FALSE
 
-- Length functions may or may not include padding
SELECT country_code, LENGTH(country_code), CHAR_LENGTH(country_code)
FROM country_codes;
-- Results vary: some DBs return 2, others return actual length without padding
 
-- Concatenation includes padding!
SELECT country_code || '-' || country_name  -- 'US-United States' or 'US -United States'?
FROM country_codes
WHERE country_code = 'US';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

-- VARCHAR type declarations
CREATE TABLE customers (
    customer_id     INTEGER PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,     -- Few names exceed 50 chars
    last_name       VARCHAR(50) NOT NULL,
    email           VARCHAR(255) NOT NULL,    -- Email spec allows up to 254
    phone           VARCHAR(20),              -- International formats vary
    address_line1   VARCHAR(100),
    address_line2   VARCHAR(100),
    city            VARCHAR(50),
    postal_code     VARCHAR(20),              -- Some countries have long codes
    notes           VARCHAR(2000)             -- Extended comments
);
 
-- Email-specific considerations
CREATE TABLE users (
    user_id         INTEGER PRIMARY KEY,
    username        VARCHAR(30) NOT NULL,     -- Enforce reasonable limits
    email           VARCHAR(255) NOT NULL UNIQUE,
    -- RFC 5321 specifies 254 max, but 255 is commonly used
    display_name    VARCHAR(100)
);
 
-- URL storage
CREATE TABLE web_pages (
    page_id         INTEGER PRIMARY KEY,
    url             VARCHAR(2083) NOT NULL,   -- IE historical limit
    -- Modern browsers support 64K+, but 2083 is safe default
    title           VARCHAR(500),
    meta_description VARCHAR(160)             -- SEO best practice
);
 
-- Demonstrating variable-length storage efficiency
-- Assume average name length is 8 characters
-- CHAR(50): 50 bytes per name × 1 million rows = 50 MB
-- VARCHAR(50): ~9 bytes per name × 1 million rows = 9 MB
-- Savings: 41 MB (82% reduction in storage for names alone)
 
-- Length validation in application or database
CREATE TABLE products (
    product_id      INTEGER PRIMARY KEY,
    product_name    VARCHAR(200) NOT NULL,
    short_desc      VARCHAR(500),
    CONSTRAINT chk_name_min_length CHECK (LENGTH(product_name) >= 3)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

-- TEXT/CLOB use cases
 
-- Document content storage
CREATE TABLE articles (
    article_id      INTEGER PRIMARY KEY,
    title           VARCHAR(200) NOT NULL,
    summary         VARCHAR(1000),
    content         TEXT NOT NULL,              -- Full article body
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- JSON storage (before native JSON types)
CREATE TABLE api_logs (
    log_id          INTEGER PRIMARY KEY,
    endpoint        VARCHAR(500) NOT NULL,
    request_body    TEXT,                       -- JSON request payload
    response_body   TEXT,                       -- JSON response payload
    logged_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Log aggregation
CREATE TABLE audit_logs (
    log_id          BIGINT PRIMARY KEY,
    log_level       VARCHAR(10) NOT NULL,
    message         TEXT NOT NULL,              -- Variable-length log messages
    stack_trace     TEXT,                       -- Exception details
    context_json    TEXT                        -- Additional structured data
);
 
-- HTML content
CREATE TABLE email_templates (
    template_id     INTEGER PRIMARY KEY,
    template_name   VARCHAR(100) NOT NULL,
    subject         VARCHAR(500) NOT NULL,
    html_body       TEXT NOT NULL,              -- Full HTML email
    text_body       TEXT                        -- Plain text version
);
 
-- PostgreSQL: TEXT is truly unlimited (1 GB practical limit)
-- No need for MEDIUMTEXT/LONGTEXT variants
 
-- MySQL: Choose based on expected size
CREATE TABLE mysql_content (
    id INT PRIMARY KEY,
    short_content TEXT,           -- Up to 64 KB
    medium_content MEDIUMTEXT,    -- Up to 16 MB
    large_content LONGTEXT        -- Up to 4 GB
);
 
-- SQL Server: VARCHAR(MAX) is preferred over deprecated TEXT
CREATE TABLE sqlserver_content (
    id INT PRIMARY KEY,
    content VARCHAR(MAX)          -- Up to 2 GB, modern approach
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

-- Database-level character set configuration
 
-- PostgreSQL: Set encoding at database creation
CREATE DATABASE myapp_db
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8';
 
-- MySQL: Database and table character sets
CREATE DATABASE myapp_db
    CHARACTER SET utf8mb4           -- Full UTF-8 (4-byte support)
    COLLATE utf8mb4_unicode_ci;     -- Case-insensitive Unicode collation
 
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) CHARACTER SET utf8mb4,
    bio TEXT CHARACTER SET utf8mb4
);
 
-- SQL Server: Uses NVARCHAR for Unicode
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username NVARCHAR(50),          -- Unicode-capable
    legacy_code VARCHAR(10)          -- Non-Unicode (uses database collation)
);
 
-- Oracle: AL32UTF8 is recommended for Unicode
ALTER DATABASE CHARACTER SET AL32UTF8;
 
-- Storage implications of encoding
-- ASCII text: 1 byte per character in any encoding
-- European accents (é, ü): 1 byte in Latin-1, 2 bytes in UTF-8
-- Asian characters (中, 日): 3 bytes in UTF-8
-- Emoji (😀): 4 bytes in UTF-8
 
-- Example: Japanese text storage
-- 'こんにちは' (5 characters)
-- UTF-8: 15 bytes (3 bytes per hiragana character)
-- NVARCHAR with UTF-16: 10 bytes (2 bytes per character)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

-- Collation effects on comparison and sorting
 
-- PostgreSQL: Collation specification
CREATE TABLE users (
    email VARCHAR(255) COLLATE "en_US" UNIQUE
);
 
-- Case-insensitive comparison (PostgreSQL)
SELECT * FROM users WHERE email = 'John@Example.com' COLLATE "en_US";
-- With case-insensitive collation: matches 'john@example.com'
 
-- MySQL: Column-level collation
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(200) 
        CHARACTER SET utf8mb4 
        COLLATE utf8mb4_unicode_ci,    -- Case-insensitive
    product_code VARCHAR(20)
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_bin             -- Binary, case-sensitive
);
 
-- Comparison behavior demonstration
-- With utf8mb4_unicode_ci:
SELECT * FROM products WHERE product_name = 'LAPTOP';
-- Matches: 'Laptop', 'laptop', 'LAPTOP', 'LaPtOp'
 
-- With utf8mb4_bin:
SELECT * FROM products WHERE product_code = 'ABC123';
-- Only matches exactly 'ABC123', not 'abc123'
 
-- SQL Server: Collation in queries
SELECT *
FROM customers
WHERE last_name = 'müller' COLLATE Latin1_General_CI_AI;
-- CI_AI = Case Insensitive, Accent Insensitive
-- Matches: 'Müller', 'MULLER', 'muller', 'Muller'
 
-- Sorting behavior varies by collation
-- German collation: ä sorts with a (Müller before Nyman)
-- Swedish collation: ä sorts after z (Müller after Zylstra)
 
SELECT last_name FROM customers
ORDER BY last_name COLLATE "de_DE";  -- German sorting
 
SELECT last_name FROM customers
ORDER BY last_name COLLATE "sv_SE";  -- Swedish sorting
 
-- Binary collation for performance
-- Faster comparisons (no linguistic rules)
-- But: 'A' ≠ 'a', 'é' ≠ 'e'
CREATE INDEX idx_username_bin ON users (username COLLATE "C");

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

-- SQL Server: VARCHAR vs NVARCHAR comparison
 
-- Non-Unicode (uses database's collation code page)
CREATE TABLE legacy_customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),          -- Cannot store characters outside code page
    last_name VARCHAR(50)
);
 
-- Unicode (supports all languages)
CREATE TABLE international_customers (
    customer_id INT PRIMARY KEY,
    first_name NVARCHAR(50),         -- Can store any Unicode character
    last_name NVARCHAR(50),
    display_name NVARCHAR(100)
);
 
-- Inserting international data
INSERT INTO international_customers (customer_id, first_name, last_name, display_name)
VALUES 
    (1, N'José', N'García', N'José García'),      -- Spanish
    (2, N'山田', N'太郎', N'山田 太郎'),           -- Japanese
    (3, N'Müller', N'Hans', N'Hans Müller'),      -- German
    (4, N'Иван', N'Петров', N'Иван Петров');      -- Russian
 
-- The N prefix is crucial for literal Unicode strings
INSERT INTO international_customers VALUES (5, N'😀', N'Emoji', N'😀 Emoji');
 
-- Without N prefix, Unicode characters may be corrupted or cause errors
INSERT INTO international_customers VALUES (6, '中文', 'Test', 'Test中文');  -- WRONG!
INSERT INTO international_customers VALUES (6, N'中文', N'Test', N'Test中文');  -- Correct
 
-- Storage comparison
-- VARCHAR: 'Hello' = 5 bytes
-- NVARCHAR: N'Hello' = 10 bytes (2 bytes per character)
 
-- Performance consideration: NVARCHAR uses 2× storage
-- For pure ASCII data, this doubles table and index sizes
 
-- Oracle: NVARCHAR2 and NCHAR
CREATE TABLE oracle_international (
    id NUMBER(10) PRIMARY KEY,
    name NVARCHAR2(100)              -- Unicode variable-length
);
 
-- PostgreSQL: No separate NVARCHAR (TEXT/VARCHAR with UTF-8 covers all)
-- Just ensure database encoding is UTF8
CREATE TABLE pg_international (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100)                -- UTF-8 handles all Unicode
);