sqlite-sql

name	sqlite-sql
description	SQLite-specific SQL patterns: substr/instr for string ops, || for concatenation, LIKE (no ILIKE), date()/strftime() for dates, CAST for type coercion, no FULL OUTER JOIN, GROUP_CONCAT, typeof(), COALESCE/IFNULL, printf() formatting.
type	skill

SQLite SQL Skill

1. String Functions - substr() and instr()

SQLite has no POSITION() or SPLIT_PART(). Use substr() and instr():

-- Extract substring starting at position 3, length 5
SELECT substr(col, 3, 5) FROM t;

-- Find position of substring (0 if not found)
SELECT instr(col, 'needle') FROM t;

-- Extract everything after a delimiter
SELECT substr(col, instr(col, '/') + 1) FROM t
WHERE instr(col, '/') > 0;

2. String Concatenation - Use || (not CONCAT)

-- Concatenate two strings
SELECT first_name || ' ' || last_name AS full_name FROM employees;

-- With NULL handling (|| propagates NULL)
SELECT COALESCE(first_name, '') || ' ' || COALESCE(last_name, '') AS full_name
FROM employees;

3. Case-Insensitive Matching - LIKE Only (no ILIKE)

SQLite's LIKE is case-insensitive for ASCII letters by default. There is no ILIKE:

-- Case-insensitive search (ASCII only by default)
WHERE name LIKE '%widget%'

-- For Unicode/non-ASCII, use UPPER/LOWER explicitly
WHERE UPPER(name) LIKE UPPER('%widget%')

4. Date Functions - date(), datetime(), strftime()

SQLite stores dates as text (ISO 8601), real, or integer. Use built-in date functions:

-- Current date / datetime
SELECT date('now');
SELECT datetime('now');

-- Add/subtract time
SELECT date('now', '+7 days');
SELECT date('now', '-1 month');
SELECT date(col, '+1 year') FROM t;

-- Truncate to month start
SELECT date(col, 'start of month') FROM t;

-- Extract parts
SELECT strftime('%Y', col) AS year FROM t;
SELECT strftime('%m', col) AS month FROM t;
SELECT strftime('%Y-%m', col) AS year_month FROM t;

-- Difference in days (days between two dates)
SELECT CAST(julianday(end_date) - julianday(start_date) AS INTEGER) AS days_diff
FROM t;

5. Type Coercion - CAST() Only (no :: syntax)

SQLite does not support the :: cast syntax. Use CAST():

-- Cast to integer
SELECT CAST(price AS INTEGER) FROM products;

-- Cast to real
SELECT CAST(score AS REAL) FROM results;

-- Cast to text
SELECT CAST(id AS TEXT) FROM records;

6. No FULL OUTER JOIN - Simulate with UNION

SQLite does not support FULL OUTER JOIN. Simulate it:

-- FULL OUTER JOIN equivalent
SELECT a.id, a.val, b.val
FROM table_a a
LEFT JOIN table_b b ON a.id = b.id
UNION
SELECT b.id, a.val, b.val
FROM table_b b
LEFT JOIN table_a a ON b.id = a.id
WHERE a.id IS NULL;

7. String Aggregation - GROUP_CONCAT

-- Comma-separated list of values per group
SELECT department, GROUP_CONCAT(name) AS members
FROM employees
GROUP BY department;

-- Custom separator
SELECT department, GROUP_CONCAT(name, ' | ') AS members
FROM employees
GROUP BY department;

-- With ordering (SQLite 3.44+, use subquery for older versions)
SELECT department,
       GROUP_CONCAT(name ORDER BY name) AS sorted_members
FROM employees
GROUP BY department;

8. Runtime Type Checking - typeof()

-- Returns 'integer', 'real', 'text', 'blob', or 'null'
SELECT typeof(col) FROM t;

-- Filter by storage class
SELECT * FROM t WHERE typeof(col) = 'integer';

9. NULL Handling - COALESCE, IFNULL, NULLIF

-- COALESCE: first non-NULL value
SELECT COALESCE(col1, col2, 'default') FROM t;

-- IFNULL: SQLite shorthand for two-argument COALESCE
SELECT IFNULL(col, 0) FROM t;

-- NULLIF: return NULL if two values are equal
SELECT NULLIF(col, 0) FROM t;   -- returns NULL when col = 0

10. Formatted Output - printf()

-- Zero-padded integer
SELECT printf('%05d', id) FROM t;

-- Fixed decimal places
SELECT printf('%.2f', price) FROM t;

-- String formatting
SELECT printf('%s-%s', category, subcategory) FROM t;

11. Common Anti-Patterns to Avoid

• No BOOLEAN type - use 0 and 1 (integers)
• No ALTER COLUMN - SQLite only supports ADD COLUMN in ALTER TABLE
• Prefer WITHOUT ROWID only for tables with non-integer primary keys
• Do NOT use AUTOINCREMENT unless you need gap-free IDs - plain INTEGER PRIMARY KEY gives auto-increment behavior and is faster
• LIKE pattern uses % (any chars) and _ (one char) - no regex by default
• IN (SELECT ...) is generally faster than correlated subqueries in SQLite
• Do NOT use = NULL - use IS NULL
• || propagates NULL - wrap with COALESCE when concatenating nullable columns

12. Benchmark Patterns

• Window functions: SQLite supports ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD since 3.25. No QUALIFY - use subquery wrapping.
• HAVING without GROUP BY: Not valid in SQLite - always pair HAVING with GROUP BY.
• Recursive CTEs: WITH RECURSIVE works in SQLite - useful for hierarchical data (org charts, category trees).
• No LIMIT in subqueries with IN: WHERE col IN (SELECT ... LIMIT N) is not supported - use a CTE instead.