Example 1
Extract the numeric error code from a log message string
The pattern [0-9]+ matches one or more consecutive digits. For 'Error code 4042 occurred' the first match is '4042'; for 'Warning 503 from gateway' it is '503'. PostgreSQL's REGEXP_MATCH returns a text array of capture groups; [1] retrieves the first element. MySQL and MariaDB return the matched substring directly. The SQL Server workaround locates the first digit with PATINDEX('%[0-9]%', ...), then extracts through the end of the digit run by finding where non-digits resume — correct for a single contiguous digit block but not a general regex engine.
CREATE TABLE logs (id INT, notes VARCHAR(200));
INSERT INTO
logs (id, notes)
VALUES
(1, 'Error code 4042 occurred'),
(2, 'Warning 503 from gateway');SELECT
id,
REGEXP_SUBSTR (notes, '[0-9]+') AS error_code
FROM
logs
ORDER BY
id;| id | error_code |
|---|---|
| 1 | 4042 |
| 2 | 503 |
CREATE TABLE logs (id INT, notes VARCHAR(200));
INSERT INTO
logs (id, notes)
VALUES
(1, 'Error code 4042 occurred'),
(2, 'Warning 503 from gateway');SELECT id, (REGEXP_MATCH(notes, '[0-9]+'))[1] AS error_code FROM logs ORDER BY id;| id | error_code |
|---|---|
| 1 | 4042 |
| 2 | 503 |
MySQL and MariaDB use REGEXP_SUBSTR(). PostgreSQL uses REGEXP_MATCH()[1]. SQL Server is unsupported natively; the workaround uses PATINDEX and SUBSTRING to extract the first contiguous digit sequence. All return the same string values.