Menu
I currently have the the following query:
select regexp_matches(name, 'foo') from table; How can I rewrite this so that the regex is in the where like the following (not working):
select * from table where regexp_matches(name, 'foo'); Current error message is: ERROR: argument of WHERE must be type boolean, not type text[] SQL state: 42804 Character: 29
612
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.
Regular Expressions, also known as RegEx are pattern matching criteria that can filter data based on the pattern. It is heavily used to match string values to a specific pattern and then filter the results based on the condition.
We can compare the string using like clause in PostgreSQL, we can also compare the string using the =, != , <>, <, >, <= and >= character string operator. Basically character string operator in PostgreSQL is used to compare the string and return the result as we specified input within the query.
SELECT * FROM spatial_ref_sys WHERE srtext LIKE '%\ /%'; Sometimes these ticks are very useful for searching special characters in a database.
Write instead:
select * from table where name ~ 'foo' The '~' operator produces a boolean result for whether the regex matches or not rather than extracting the matching subgroups.
115
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
