These websites provides a very useful list of symbols and syntax used in regular expressions, and contains examples and exercises.
https://librarycarpentry.org/lc-data-intro/01-regular-expressions.html
Tutorial: https://regex101.com/
Case example of using regular expressions to extract a component of a field that can't be parsed out via a json extract call (thank you to Jean Pajerek for this):
The "value" field of the configuration_entries table contains many extraneous characters. In this example, we need to extract the "name" component:
SELECT
cfge.id AS bill_to_id,
substring (cfge.value, '([A-Z].+)(",".+)') as bill_to_name
FROM
configuration_entries AS cfge
WHERE cfge.value LIKE '{"name"%'
- This regular expression says: select the substring that meets the following pattern:
- the first component starts with any letter and is followed by any number of other characters (the dot is a wildcard and the plus means any number of them);
- The second component begins with the character sequence "," and is followed by any number of other characters
- the Where clause stipulates that the "value" field must begin with the character sequence '{"name"%'