Flag g causes the function to find each match in the string, not only the first one, and return a row for each such match. It is heavily used to match string values to a specific pattern and then filter the results based on the condition.
![postgresql regex postgresql regex](https://res.cloudinary.com/dyd911kmh/image/upload/f_auto,q_auto:best/v1548964520/Capture_2_gmx6l3.png)
Regular Expressions, also known as RegEx are pattern matching criteria that can filter data based on the pattern.
![postgresql regex postgresql regex](https://www.postgresqltutorial.com/wp-content/uploads/2015/09/postgresql-replace-in-database.jpg)
The flags parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. In this article, I am going to talk about using regular expressions in a Postgres database. If the pattern contains parenthesized subexpressions, the function returns a text array whose n'th element is the substring matching the n'th parenthesized subexpression of the pattern (not counting "non-capturing" parentheses see below for details). If the pattern contains no parenthesized subexpressions, then each row returned is a single-element text array containing the substring matching the whole pattern. If the pattern does not match, the function returns no rows. The function can return no rows, one row, or multiple rows (see the g flag below). It has the syntax regexp_matches(string, pattern ). The regexp_matches function returns a text array of all of the captured substrings resulting from matching a POSIX regular expression pattern. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g., E'foo'. PostgreSQL also accepts ' escape ' string constants, which are an extension to the SQL standard.
![postgresql regex postgresql regex](https://i.stack.imgur.com/fVwCU.png)
SELECT regexp_matches(column,'^stuff.*$') In postgreSQL you can specify the escape character by prefixing the letter E.