Functions and Operators

-- search substring

position('om' in 'Thomas')   =  3

-- substring by position

substring('Thomas' from 2 for 3) = hom

-- matching pattern, # as delimiter, use #" in pattern, pattern must match whole string

substring('foobar' from '%#"o_b#"%' for '#') =  oob

-- matching regular expression, () indicates re to fetch, (?:re) is non-reporting re that only group but not fetch

substring('foobar' from 'o.b')     oob

substring('foobar' from 'o(.)b')   o

substring(category from '^(.*?)(?:: .*)?$') as mcat, 

substring('Thomas' from '...$') = 'mas'