Support for Regular Expressions in DBMS's (DataBase Management Systems)

Pattern matching and text replacement

There are three main tasks people usually solve using regular expressions:

• Match a string against the pattern.
• Extract a part of the string that matches the pattern.
• Replace all parts of the string that match the pattern.

sqlean-regexp provides a separate function for each of these tasks.

regexp_like

Checks if the source string matches the pattern.

select regexp_like('Meet me at 10:30', '\d+:\d+');
-- 1
select regexp_like('Meet me at the cinema', '\d+:\d+');
-- 0

regexp_substr

Returns a substring of the source string that matches the pattern.

select regexp_substr('Meet me at 10:30', '\d+:\d+');
-- 10:30
select regexp_substr('Meet me at 17:05', '\d+:\d+');
-- 17:05

regexp_replace

Replaces all matching substrings with the replacement string.

select regexp_replace('password = "123456"', '"[^"]+"', '***');
-- password = ***
select regexp_replace('1 2 3 4', '[2468]', 'even');
-- 1 even 3 even

Pattern syntax

sqlean-regexp supports pretty advanced syntax, including various groups, lazy quantifiers, and look-arounds:

select regexp_substr('the year is 2020', '(\d{2})\1');
-- 2020
select regexp_substr('the year is 2021', '(\d{2})\1');
-- (null)

select regexp_substr('1 2 3 2 4 5', '.*2');
-- 1 2 3 2
select regexp_substr('1 2 3 2 4 5', '.*?2');
-- 1 2

select regexp_substr('new year', '(\w+)\s(?=year)');
-- new
select regexp_substr('last year', '(\w+)\s(?=year)');
-- last

Some Practical Examples

Given a table named sample_data...

Match strings starting with ‘a’

SELECT * FROM sample_data WHERE regex REGEXP ‘^a’;

Match strings ending with ‘e’

SELECT * FROM sample_data WHERE regex REGEXP ‘e$’;

Match strings containing ‘12’

SELECT * FROM sample_data WHERE regex REGEXP ‘12’;

Match strings starting with either ‘a’ or ‘o’

SELECT * FROM sample_data WHERE regex REGEXP ‘^(a|o)’;

Match strings with at least one digit

SELECT * FROM sample_data WHERE regex REGEXP ‘[0–9]’;

Match strings with exactly 5 uppercase letters

SELECT * FROM sample_data WHERE regex REGEXP ‘^[A-Z]{5}$’;

Match strings with at least one letter

SELECT * FROM sample_data WHERE regex REGEXP ‘[a-zA-Z]’;

Match strings containing ‘cat’ or ‘dog’ (case-insensitive)

SELECT * FROM sample_data WHERE regex REGEXP ‘(cat|dog)’;

Match strings not containing ‘abc’

SELECT * FROM sample_data WHERE regex NOT REGEXP ‘abc’;

Match strings with at least one uppercase letter, followed by any character, and ending with two digits

SELECT * FROM sample_data WHERE regex REGEXP ‘[A-Z].*[0–9]{2}’;

Match strings starting with ‘abc’ or ‘xyz’, followed by any number of digits, and optionally ending with ‘_test’

SELECT * FROM sample_data WHERE regex REGEXP ‘^(abc|xyz)[0–9]*(_test)?$’;

REGEXP_CONTAINS(expression, pattern)

This function has an alias REGEX_CONTAINS().

It returns TRUE if the string value contains any sequence that matches the regular expression pattern.

Example:

Query:

SELECT name
FROM landmark
WHERE REGEXP_CONTAINS(name, "In+.*")
LIMIT 5;

Result:

[
  {
    "name": "Beijing Inn"
  },
  {
    "name": "Sportsman Inn"
  },
  {
    "name": "In-N-Out Burger"
  },
  {
    "name": "Mel's Drive-In"
  },
  {
    "name": "Inverness Castle"
  }
]

REGEXP_LIKE(expression, pattern)

This function has an alias REGEX_LIKE().

It returns TRUE if the string value exactly matches the regular expression pattern.

Example:

Query:

SELECT name
FROM landmark
WHERE REGEXP_LIKE(name, "In+.*")
LIMIT 5;

Result:

[
  {
    "name": "In-N-Out Burger"
  },
  {
    "name": "Inverness Castle"
  },
  {
    "name": "Inverness Museum & Art Gallery"
  },
  {
    "name": "Inverness Botanic Gardens"
  },
  {
    "name": "International Petroleum Exchange"
  }
]

REGEXP_MATCHES(expression, pattern)

Returns an array of all substrings matching the expression pattern within the input string expression. Returns an empty array if no match is found.

The following query finds all words beginning with upper or lower case B.

Query:

SELECT REGEXP_MATCHES("So, 'twas better Betty Botter bought a bit of better butter",
                      "\\b[Bb]\\w+");

Result:

[
  {
    "$1": [
      "better",
      "Betty",
      "Botter",
      "bought",
      "bit",
      "better",
      "butter"
    ]
  }
]

The following query finds sequences of two words beginning with upper or lower case B.

Query:

SELECT REGEXP_MATCHES("So, 'twas better Betty Botter bought a bit of better butter",
                      "\\b[Bb]\\w+ \\b[Bb]\\w+");

Result:

[
  {
    "$1": [
      "better Betty",
      "Botter bought",
      "better butter"
    ]
  }
]

REGEXP_POSITION(expression, pattern)

This function has an alias REGEX_POSITION().

Returns first position of the occurrence of the regular expression pattern within the input string expression. Returns -1 if no match is found. Position counting starts from zero.

Query:

SELECT name, ARRAY REGEXP_POSITION(x, "[aeiou]") FOR x IN TOKENS(name) END
FROM hotel
LIMIT 2;

Result:

[
  {
    "$1": [
      1,
      1,
      1
    ],
    "name": "Medway Youth Hostel"
  },
  {
    "$1": [
      2,
      1,
      1
    ],
    "name": "The Balmoral Guesthouse"
  }
]

REGEXP_REPLACE(expression, pattern, repl [, n])

This function has an alias REGEX_REPLACE().

repl is a string, or any SQL++ expression that evaluates to a string.

n [optional] is the maximum number of times to find and replace the matching pattern.

It returns new string with occurrences of pattern replaced with repl. If n is given, at the most n replacements are performed. If n is not provided, all matching occurrences are replaced.

Examples:

Query:

SELECT REGEXP_REPLACE("Sql++ is sql for NoSql", "[sS][qQ][lL]", "SQL"),
       REGEXP_REPLACE("Winning innings Inn", "[Ii]n+", "Hotel", 6),
       REGEXP_REPLACE("Winning innings Inn", "[IiNn]+g", upper("inning"), 2);

Result:

[
  {
    "$1": "SQL++ is SQL for NoSQL",
    "$2": "WHotelHotelg HotelHotelgs Hotel",
    "$3": "WINNING INNINGs Inn"
  }
]

In the example below, the query retrieves first 4 documents and replaces the pattern of repeating n with emphasized NNNN.

Query:

SELECT name, REGEXP_REPLACE(name, "n+", "NNNN") as new_name
FROM airline
LIMIT 4;

Result:

[
  {
    "name": "40-Mile Air",
    "new_name": "40-Mile Air"
  },
  {
    "name": "Texas Wings",
    "new_name": "Texas WiNNNNgs"
  },
  {
    "name": "Atifly",
    "new_name": "Atifly"
  },
  {
    "name": "Jc royal.britannica",
    "new_name": "Jc royal.britaNNNNica"
  }
]

REGEXP_SPLIT(expression, pattern)

Returns an array of all the substrings created by splitting the input string expression at each occurrence of the expression pattern. Returns an empty array if no match is found.

Example:

Query:

SELECT REGEXP_SPLIT("C:\\Program Files\\couchbase\\server\\bin", "[\\\\]") AS Windows,
REGEXP_SPLIT("/opt/couchbase/bin", "/") AS Unix;

Result:

[
  {
    "Unix": [
      "",
      "opt",
      "couchbase",
      "bin"
    ],
    "Windows": [
      "C:",
      "Program Files",
      "couchbase",
      "server",
      "bin"
    ]
  }
]