Generate SQL regex queries

-- PostgreSQL (POSIX regex)
-- Match in WHERE
SELECT * FROM logs WHERE message ~ '\\b\\w+@\\w+\\.\\w+\\b';

-- First match (scalar)
SELECT (regexp_match(message, '\\b\\w+@\\w+\\.\\w+\\b'))[1] AS first FROM logs;

-- All matches (returns set of arrays)
SELECT regexp_matches(message, '\\b\\w+@\\w+\\.\\w+\\b', 'g') FROM logs;

-- Replace
SELECT regexp_replace(message, '\\b\\w+@\\w+\\.\\w+\\b', '[email]', 'g') FROM logs;

-- Split
SELECT regexp_split_to_array(message, '\\b\\w+@\\w+\\.\\w+\\b') FROM logs;
SELECT regexp_split_to_table(message, '\\b\\w+@\\w+\\.\\w+\\b') FROM logs;

-- MySQL 8+ / MariaDB (ICU regex)
-- Match in WHERE (case-insensitive needs explicit flag or collation)
SELECT * FROM logs WHERE message REGEXP '\\b\\w+@\\w+\\.\\w+\\b';

-- REGEXP_LIKE with match-type flag (i, c, m, n, u)
SELECT * FROM logs WHERE REGEXP_LIKE(message, '\\b\\w+@\\w+\\.\\w+\\b', 'c');

-- First match
SELECT REGEXP_SUBSTR(message, '\\b\\w+@\\w+\\.\\w+\\b', 1, 1, 'c') AS first FROM logs;

-- Nth match (position 1, occurrence n)
SELECT REGEXP_SUBSTR(message, '\\b\\w+@\\w+\\.\\w+\\b', 1, 2, 'c') AS second FROM logs;

-- Replace all
SELECT REGEXP_REPLACE(message, '\\b\\w+@\\w+\\.\\w+\\b', '[email]', 1, 0, 'c') FROM logs;

-- Position of first match
SELECT REGEXP_INSTR(message, '\\b\\w+@\\w+\\.\\w+\\b', 1, 1, 0, 'c') FROM logs;

-- SQLite REGEXP is an OPTIONAL extension — not built in by default.
-- Load it first, e.g. with the sqlite3 CLI:
--     .load ./regex
-- Or use a driver that registers a Python/Go/Rust regex callback.

-- Match
SELECT * FROM logs WHERE message REGEXP '\\b\\w+@\\w+\\.\\w+\\b';

-- Negated
SELECT * FROM logs WHERE message NOT REGEXP '\\b\\w+@\\w+\\.\\w+\\b';

-- SQLite has no built-in regexp_replace. Two options:
--   1) Load a regex extension that provides regexp_replace().
--   2) Pre-process the data in your application layer.

-- Caveat: SQL LIKE is NOT regex.
--   LIKE uses % (any) and _ (single char) — escape with ESCAPE.
--   SELECT * FROM logs WHERE message LIKE '%error%';
-- For regex semantics, use REGEXP (extension) or filter in app code.