SELECT load_extension('extension_name');

-- Generate UUIDs
SELECT uuid4_str();
-- '550e8400-e29b-41d4-a716-446655440000'

SELECT uuid_str(uuid4());
-- 'f47ac10b-58cc-4372-a567-0e02b2c3d479'

-- UUID v7 is time-ordered (good for primary keys)
SELECT uuid_str(uuid7());
-- '0190a5c0-1234-7abc-8def-0123456789ab'

-- Extract timestamp from UUID v7
SELECT uuid7_timestamp_ms(uuid7());
-- 1720000000000

-- Use in a table
CREATE TABLE documents (
    id BLOB PRIMARY KEY DEFAULT (uuid7()),
    title TEXT
);
INSERT INTO documents (title) VALUES ('My Document');
SELECT uuid_str(id), title FROM documents;

-- Pattern matching
SELECT regexp('[0-9]+', 'abc123def');
-- 1

-- The REGEXP operator uses this extension
SELECT 'hello123' REGEXP '[a-z]+[0-9]+';
-- 1

-- Extract matching substring
SELECT regexp_substr('Price: $42.50', '[0-9]+\.[0-9]+');
-- '42.50'

-- Capture groups
SELECT regexp_capture('2025-03-15', '(\d{4})-(\d{2})-(\d{2})', 1);
-- '2025'

-- Replace
SELECT regexp_replace('Hello World', '[aeiou]', '*');
-- 'H*ll* W*rld'

CREATE TABLE docs (
    id INTEGER PRIMARY KEY,
    content TEXT,
    embedding BLOB
);

INSERT INTO docs VALUES (1, 'database systems', vector32('[0.1, 0.2, 0.3]'));
INSERT INTO docs VALUES (2, 'machine learning', vector32('[0.4, 0.5, 0.6]'));

-- Find similar documents
SELECT content, vector_distance_cos(embedding, vector32('[0.1, 0.25, 0.35]')) AS dist
FROM docs
ORDER BY dist
LIMIT 5;

-- Current time formatted
SELECT time_fmt_iso(time_now());
-- '2025-03-15T10:30:00Z'

-- Date arithmetic
SELECT time_fmt_date(time_add_date(time_now(), 0, 1, 0));
-- add 1 month to today

-- Duration since a timestamp
SELECT time_since(time_date(2025, 1, 1, 0, 0, 0)) / dur_h();
-- hours since Jan 1, 2025

-- Create an FTS index
CREATE INDEX idx ON articles USING fts (title, body);

-- Search
SELECT title, fts_score('idx') AS score
FROM articles
WHERE fts_match('idx', 'database search')
ORDER BY score;

-- Highlighted results
SELECT fts_highlight('idx', 0, '<b>', '</b>') AS title
FROM articles
WHERE fts_match('idx', 'database');

CREATE VIRTUAL TABLE temp.csv_data USING csv(
    filename='/path/to/data.csv',
    header=yes
);

SELECT * FROM csv_data;

CREATE TABLE scores (value REAL);
INSERT INTO scores VALUES (10), (20), (30), (40), (50);

SELECT median(value) FROM scores;
-- 30.0

SELECT percentile(value, 75) FROM scores;
-- 40.0

SELECT percentile_cont(value, 0.25) FROM scores;
-- 20.0

SELECT value FROM generate_series(start, stop);
SELECT value FROM generate_series(start, stop, step);

SELECT value FROM generate_series(1, 5);
-- 1, 2, 3, 4, 5

SELECT value FROM generate_series(0, 10, 2);
-- 0, 2, 4, 6, 8, 10

SELECT value FROM generate_series(10, 1, -3);
-- 10, 7, 4, 1