A regular expression is a character sequence that is an abbreviated definition of a set of strings (a regular set).
Regular Expressions have long been widely used in programming languages, but utilizing them in a SQL statement makes the query highly dynamic – and fun!
Here’s a quick example where I want to remove all repeating characters from a string….
yugabyte=# CREATE TABLE regex(c1 TEXT);
CREATE TABLE
yugabyte=# INSERT INTO regex VALUES ('ZZ Z Z Z'), ('A B C A B'), ('AAA BBB CCC');
INSERT 0 3
yugabyte=# SELECT c1, regexp_replace(c1, '(.)\1{1,}', '\1', 'g') c1_csc_removed FROM regex;
c1 | c1_csc_removed
-------------+----------------
AAA BBB CCC | A B C
A B C A B | A B C A B
ZZ Z Z Z | Z Z Z Z
(3 rows)
Have Fun!