Regular expressions

Find, count, cut out and replace text with patterns like \d+ (one or more digits).

• Category: Functions — text
• Keywords: regex, regexp, regular expression, pattern, pattern matching, flags, capture group, groups, back reference, look-ahead, lazy, greedy, ~, RLIKE, REGEXP, regexp_match, regexp_matches, regexp_split_to_array, regexp_split_to_table, split with a pattern, regexp_instr, validate email, Nepali digits
• See also: Text functions, WHERE, Full-text search

A regular expression (regex) is a small pattern that describes text. \d+ means "one or more digits". ^[A-Z]{2,8}$ means "only 2 to 8 capital letters". Use them to check what a user typed, to find numbers in a news article, or to clean text.

• A NULL argument gives NULL.
• Upper and lower case must match, unless you give the flag 'i'.
• The pattern is found anywhere in the text. Put ^ at the start and $ at the end to check the whole text.
• KDB never backtracks (tries the same text again and again). So no pattern can hang the server. A very big job stops with an error.
• Simple searches are easier with LIKE and ILIKE. For searching words in many rows (a forum, news), use full-text search. A regular expression reads every row.
• x ~ 'pattern' is the same as regexp_like(x, 'pattern') (~* ignores case). SIMILAR TO is a whole-text match written with % and _.

Example: Percent numbers people wrote in forum posts

Setup:

CREATE TABLE forum_posts (id LONG PRIMARY KEY AUTO, body STRING NOT NULL);
INSERT INTO forum_posts (body) VALUES
  ('Profit is up 18%. I think the price will go up.'),
  ('But NPL is still 2.6%. Wait for the next quarter.'),
  ('नाफा राम्रो छ, तर मूल्य पहिले नै बढिसकेको छ।'),
  ('NPL went down from 2.91% to 2.6%. That is good news.');

SELECT id,
       regexp_substr(body, '\d+(\.\d+)?%') AS first_percent,
       regexp_count(body, '\d+(\.\d+)?%') AS percents
FROM forum_posts
WHERE regexp_like(body, '\d%')
ORDER BY id;

Result:

(3 rows)

regexp_like

Syntax:

regexp_like(text, pattern)
regexp_like(text, pattern, flags)

true when the pattern is found somewhere in the text.

• Returns: BOOL.
• NULL: NULL when any argument is NULL.
• Notes: a wrong pattern is an error, not false. The flag 'g' is not allowed here. For a simple "contains" check, LIKE '%text%' is enough.

Example

SELECT regexp_like('CITY', '^[A-Z]{2,8}$') AS a, regexp_like('city', '^[A-Z]{2,8}$') AS b, regexp_like('city', '^[A-Z]{2,8}$', 'i') AS ignore_case, regexp_like('CITY', 'IT') AS anywhere;

Result:

(1 row)

Example: Check an email and a mobile number (Nepal format) when a user registers

SELECT email, phone,
       regexp_like(email, '^[\w.+-]+@[\w-]+(\.[\w-]+)+$') AS email_ok,
       regexp_like(phone, '^9[78]\d{8}$') AS phone_ok
FROM (SELECT 'ram.sharma@example.com' AS email, '9841234567' AS phone
      UNION ALL SELECT 'sita@@mail', '98412345'
      UNION ALL SELECT 'hari@northwind.example.com', '9761234567') AS signups;

Result:

(3 rows)

Example: Forum posts written in Nepali (Devanagari letters)

Setup:

CREATE TABLE forum_posts (id LONG PRIMARY KEY AUTO, body STRING NOT NULL);
INSERT INTO forum_posts (body) VALUES ('Profit is up 18%.'), ('नाफा राम्रो छ।'), ('CITY को मूल्य बढ्यो');

SELECT id, body FROM forum_posts WHERE regexp_like(body, '[ऀ-ॿ]') ORDER BY id;

Result:

(2 rows)

Example: Look-ahead is not supported (shows an error)

SELECT regexp_like('CITY', 'CI(?=TY)');

Result:

Error: line 1, column 8: regexp_like: regular expression 'CI(?=TY)': (?...) forms other than (?:...) are not supported

regexp_substr

Syntax:

regexp_substr(text, pattern)
regexp_substr(text, pattern, flags)

The first part of the text that matches the pattern. NULL when nothing matches.

• Returns: STRING, or NULL when there is no match.
• NULL: NULL when any argument is NULL.
• Notes: it always gives the whole match; there is no argument for a group. To get only group 1, use regexp_replace(text, '^.*?(pattern).*$', '\1') (see the example below). The flag 'g' is not allowed. Oracle and MySQL have more arguments (position, occurrence); KDB does not.

Example

SELECT regexp_substr('EPS 12.50, BVPS 245.3', '\d+\.\d+') AS first_number, regexp_substr('no digits', '\d+') AS none, regexp_substr('City city', 'CITY', 'i') AS ignore_case;

Result:

(1 row)

Example: Find the email address in news articles

Setup:

CREATE TABLE news (id LONG PRIMARY KEY AUTO, title STRING NOT NULL, body STRING NOT NULL);
INSERT INTO news (title, body) VALUES
  ('City Bank profit rises 18%', 'City Bank reported a net profit of $3.27 billion for Q4 of 2082/83.'),
  ('Volt River sells record energy', 'The plant produced 2105 GWh this year. Contact: info@voltriver.example.com');

SELECT id, regexp_substr(body, '[\w.]+@[\w.]+\w') AS email
FROM news
WHERE regexp_like(body, '\w+@\w+');

Result:

(1 row)

Example: Only the number, without the % sign (a group)

SELECT regexp_substr('Profit is up 18.5% this year', '\d+(\.\d+)?%') AS with_sign,
       regexp_replace('Profit is up 18.5% this year', '^.*?(\d+(\.\d+)?)%.*$', '\1') AS group_1;

Result:

(1 row)

regexp_count

Syntax:

regexp_count(text, pattern)
regexp_count(text, pattern, flags)

How many times the pattern is found in the text. The matches do not overlap: after a match, the search goes on after its end.

• Returns: LONG. 0 when nothing matches.
• NULL: NULL when any argument is NULL.
• Notes: a pattern that can match empty text also counts the empty places: regexp_count('abc', '') is 4.

Example

SELECT regexp_count('a1b22c333', '\d+') AS numbers, regexp_count('a1b22c333', '\d') AS digits, regexp_count('CITY city', 'city', 'i') AS ignore_case, regexp_count('abc', '') AS empty_pattern;

Result:

(1 row)

Example: Words in English and Nepali posts

Setup:

CREATE TABLE forum_posts (id LONG PRIMARY KEY AUTO, body STRING NOT NULL);
INSERT INTO forum_posts (body) VALUES ('Profit is up 18%. I think the price will go up.'), ('सिटी बैंकको नाफा १८ प्रतिशतले बढ्यो।');

SELECT id, regexp_count(body, '\w+') AS words, length(body) AS letters FROM forum_posts ORDER BY id;

Result:

(2 rows)

Example: Hashtags in a post

SELECT regexp_count('Buy #CITY and #VOLT now #market', '#\w+') AS tags,
       regexp_substr('Buy #CITY and #VOLT now #market', '#\w+') AS first_tag;

Result:

(1 row)

regexp_replace

Syntax:

regexp_replace(text, pattern, replacement)
regexp_replace(text, pattern, replacement, flags)

Replaces the first match of the pattern with replacement. With the flag 'g' (global), it replaces every match.

• Returns: STRING. The text is unchanged when nothing matches.
• NULL: NULL when any argument is NULL.
• Notes: without 'g' only the first match changes (like PostgreSQL; in MySQL every match changes). A group that took no part (or does not exist, like \9 in a pattern with 1 group) puts in empty text. A group is a part of the pattern in ( ); groups are numbered by their ( from left to right. When 'i' and 'c' are both given, the last one wins.

Example

SELECT regexp_replace('a1b2c3', '\d', '#') AS first_only, regexp_replace('a1b2c3', '\d', '#', 'g') AS every, regexp_replace('City CITY', 'city', 'X', 'gi') AS ignore_case, regexp_replace('CITY', 'x', 'y') AS no_match;

Result:

(1 row)

Example: Clean spaces in text a user typed

SELECT '[' || regexp_replace(trim('  City    Bank   Limited '), '\s+', ' ', 'g') || ']' AS cleaned;

Result:

(1 row)

Example: Groups: turn 2082/83 into 2082-2083

SELECT regexp_replace('FY 2082/83', '(\d{2})(\d{2})/(\d{2})', '\1\2-\1\3') AS fiscal_year,
       regexp_replace('CITY', '.', '[\&]', 'g') AS whole_match;

Result:

(1 row)

Example: A URL part (slug) from a news title, and text without HTML tags

SELECT lower(trim(regexp_replace('City Bank: Q4 Results!', '[^a-z0-9]+', '-', 'gi'), '-')) AS slug,
       regexp_replace('<p>Index <b>closes</b> at 2,840</p>', '<[^>]*>', '', 'g') AS plain_text,
       CAST(regexp_replace('$1,234,567', '[^0-9]', '', 'g') AS LONG) AS amount;

Result:

(1 row)

Example: Unknown flag (shows an error)

SELECT regexp_replace('a1b2', '\d', '#', 'x');

Result:

Error: line 1, column 8: regexp_replace: unknown flag 'x'; use 'i' to ignore case and 'g' to replace every match

Flags

Syntax:

regexp_like(text, pattern, 'i')
regexp_replace(text, pattern, replacement, 'gi')

The last argument of the regexp functions is a text of flags. Each letter is one flag.

• NULL: a NULL flags text gives NULL.
• Notes: an empty text '' means no flags. Any other letter is an error. PostgreSQL flags like n, s, x or m are not supported here; regexp_match and friends take PostgreSQL flags. There are no flags inside the pattern: (?i) is an error.

Example

SELECT regexp_like('market', 'MARKET') AS default_case, regexp_like('market', 'MARKET', 'i') AS i_flag, regexp_like('market', 'MARKET', 'ic') AS last_wins, regexp_replace('a-b-c', '-', '+', 'g') AS g_flag;

Result:

(1 row)

Example: The flag g only works in regexp_replace (shows an error)

SELECT regexp_like('CITY', 'T', 'g');

Result:

Error: line 1, column 8: regexp_like: unknown flag 'g'; use 'i' to ignore case

regexp_match

Syntax:

regexp_match(text, pattern)
regexp_match(text, pattern, flags)

The first match, as PostgreSQL gives it: an array of what each (...) group caught, or of the whole match when the pattern has no groups. NULL when nothing matches.

• Returns: an array of STRING (a JSON list; {a,b} on the Postgres port). A group that took no part is NULL.
• Notes: take one part with [n]: (regexp_match('abc123', '(\d+)'))[1] is 123. A pattern written in the query is compiled once for all rows.

Example

SELECT regexp_match('abc123', '(\d+)') AS groups,
       (regexp_match('abc123', '(\d+)'))[1] AS digits,
       regexp_match('foobarbequebaz', 'bar.*que') AS whole,
       regexp_match('abc', 'x') AS no_match;

Result:

(1 row)

Example: Split a log line into its parts

SELECT m[1] AS method, m[2] AS path, m[3]::int AS status
FROM (SELECT regexp_match('GET /api/quotes 200', '^(\w+) (\S+) (\d+)$') AS m) t;

Result:

(1 row)

regexp_matches

Syntax:

regexp_matches(text, pattern)
regexp_matches(text, pattern, flags)

Like regexp_match, but it gives rows: one row (an array) per match with the flag g, at most one without it, and no row when nothing matches.

• Returns: rows of arrays of STRING. In FROM the column is named after the function or the alias: FROM regexp_matches(...) AS m.
• Notes: in FROM the arguments must be constants; for a column write SELECT id, regexp_matches(col, ...) FROM t. An empty match moves on by one character, as in PostgreSQL.

Example

SELECT m[1] AS key, m[2] AS value
FROM regexp_matches('k1=v1, k2=v2', '(\w+)=(\w+)', 'g') AS m;

Result:

(2 rows)

Example

SELECT count(*) AS digits FROM regexp_matches('a1b2c3', '\d', 'g');

Result:

(1 row)

regexp_split_to_array, regexp_split_to_table

Syntax:

regexp_split_to_array(text, pattern [, flags])
regexp_split_to_table(text, pattern [, flags])

Cuts the text wherever the pattern matches. _to_array gives one list, _to_table one row per piece.

• Returns: an array of STRING, or rows of STRING.
• Notes: an empty text gives one empty piece. As in PostgreSQL an empty match only cuts between characters: regexp_split_to_array('hello world', '\s*') gives the 10 letters. The flag g is refused.

Example

SELECT regexp_split_to_array('a,b;c', '[,;]') AS parts,
       array_length(regexp_split_to_array('a,b;c', '[,;]'), 1) AS how_many;

Result:

(1 row)

Example

SELECT word FROM regexp_split_to_table('the quick  brown fox', '\s+') AS t(word);

Result:

(4 rows)

PostgreSQL flags

regexp_match, regexp_matches and the split functions take PostgreSQL's flag letters:

• Errors: PostgreSQL's flags n, m, p, w, x, b and e are refused (SQLSTATE 0A000), because they would change what matches. Any other letter: "invalid regular expression option" (22023). A wrong pattern: SQLSTATE 2201B.

Example

SELECT regexp_match('ABC', 'b', 'i') AS i_flag, regexp_match('a.c', '.', 'q') AS q_flag;

Result:

(1 row)

Regular expression syntax

Syntax:

.   [abc]   [^abc]   [a-z]   [[:alpha:]]   \d \w \s \D \W \S
^   $   \b   \B
(group)   (?:group)   a|b
*   +   ?   {n}   {n,}   {n,m}   *?   +?   ??   {n,m}?
\.  \(  \n  \t  \r  \f  \v  \uXXXX

What you can write in a pattern. One character is one letter, also a Nepali letter or sign.

Not supported (you get a clear error):

• Notes: \d is only 0-9. For Nepali digits write [०-९], or [0-9०-९] for both. ^ and $ do not match at line breaks inside the text. A { that does not start a count like {2} is a normal character. [[:alpha:]], [[:upper:]] and [[:lower:]] are English letters only; \w also has Nepali letters.
• Limits: a pattern can be at most 4096 bytes, have at most 32 numbered groups and counts up to {1000}. A pattern that becomes too big, or needs too much work for a very long text, stops with an error.
• In SQL text, a backslash is a normal character. Write '\d+' with one backslash. To put ' in the pattern, write it twice: 'it''s'.

Example: Nepali and English digits

SELECT regexp_like('१८', '\d') AS d_nepali, regexp_like('18', '\d') AS d_english,
       regexp_substr('नाफा १८ प्रतिशत', '[०-९]+') AS nepali_number,
       regexp_count('सिटी बैंकको नाफा १८ प्रतिशतले बढ्यो।', '\w+') AS nepali_words;

Result:

(1 row)

Example: Greedy and lazy, and the first alternative wins

SELECT regexp_substr('<b>CITY</b> and <b>COAST</b>', '<b>.*</b>') AS greedy,
       regexp_substr('<b>CITY</b> and <b>COAST</b>', '<b>.*?</b>') AS lazy,
       regexp_substr('CITY', 'CI|CITY') AS first_alternative,
       regexp_like('CITY Bank', '\bBank') AS word_edge,
       regexp_like('CITY Bank', '\bITY') AS no_word_edge;

Result:

(1 row)

Example: ^ and $ are the start and end of the whole text

SELECT regexp_like('line one
line two', '^line two') AS start_of_line_2,
       regexp_like('line one
line two', 'one.line') AS dot_matches_line_break;

Result:

(1 row)

Example: Back references are not supported (shows an error)

SELECT regexp_like('CITY CITY', '(\w+) \1');

Result:

Error: line 1, column 8: regexp_like: regular expression '(\w+) \1': back references like \1 are not supported in a pattern

Example: A broken pattern (shows an error)

SELECT regexp_substr('CITY', '(CI');

Result:

Error: line 1, column 8: regexp_substr: regular expression '(CI': '(' has no matching ')'

Example: Counts above 1000 are refused (shows an error)

SELECT regexp_like('CITY', 'A{2000}');

Result:

Error: line 1, column 8: regexp_like: regular expression 'A{2000}': a count in {n,m} is larger than 1000