Menu
How can the matched regular expression be returned from an SQL select? I tried using REGEXP_EXTRACT with no luck (function not available). What I've done that does work is this:
SELECT column ~ '^stuff.*$' FROM table; but this gives me a list of true / false. I want to know what is extracted in each case.
882
Capturing groups are a way to treat multiple characters as a single unit. They are created by placing the characters to be grouped inside a set of parentheses. For example, the regular expression (dog) creates a single group containing the letters "d", "o", and "g".
Use the substring() Function to SELECT if String Contains a Substring Match in PostgreSQL. The substring() returns the strings similar to abc in our case or contains abc . We then match the returned results to the str using the ~~ operator, short for like , and if they match, we select the results from the table.
tl;dr non-capturing groups, as the name suggests are the parts of the regex that you do not want to be included in the match and ?: is a way to define a group as being non-capturing. Let's say you have an email address [email protected] . The following regex will create two groups, the id part and @example.com part.
The simplest use of regex in PostgreSQL is the ~ operator, and its cousin the ~* operator. value ~ regex tests the value on the left against the regex on the right and returns true if the regex can match within the value. Note that the regex does not have to fully match the whole value, it just has to match a part.
If you're trying to capture the regex match that resulted from the expression, then substring would do the trick:
select substring ('I have a dog', 'd[aeiou]g') Would return any match, in this case "dog."
I think the missing link of what you were trying above was that you need to put the expression you want to capture in parentheses. regexp_matches would work in this case (had you included parentheses around the expression you wanted to capture), but would return an array of text with each match. If it's one match, substring is sort of convenient.
So, circling back to your example, if you're trying to return stuff if and only if it's at the beginning of a column:
select substring (column, '^(stuff)') or
select (regexp_matches (column, '^(stuff)'))[1] If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
