Using Regular Expressions in Postgres Text Fields

Regular expressions are a set of key combinations that allow people to search for specific characters, thus allowing a wide variety of control in a text environment. They are a common feature of many applications, including text editors , Command-line utilities, and programming languages ​​to search and manipulate text bodies based on certain patterns.

See the examples below:
The simplest query uses the operator, ~, followed by characters quoted literals. These examples return all records that contain specific characters (s) as defined by the criteria:

SELECT register FROM table WHERE register ~ ‘1’;
SELECT register FROM table WHERE register ~ ‘a’;
SELECT register FROM table WHERE register ~ ‘A’;
SELECT register FROM table WHERE register ~ ‘3a’;

The addition of *, after ~ makes the search query case-insensitive

SELECT register FROM table WHERE register ~* ‘a’;
SELECT register FROM table WHERE register ~* ‘3a’;

The,!, Modifies the ~ operator and excludes strings containing the character (s):
SELECT record FROM table WHERE record! ~ ‘1’;
Here are some more examples of searching for all the letters of the alphabet:
SELECT record FROM table WHERE record ~ ​​’[a-z]’
And here only numbers:
SELECT record FROM table WHERE record ~ ​​’[^ 0–9]’