Page History

...

https://librarycarpentry.org/lc-data-intro/01-regular-expressions/

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:

Image Added

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"%'