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"%'
  • No labels