Then because no group is capturing, instead the. To get the entire matching data, the regex should have a question mark and a colon ( :) added at the beginning of the regex pattern to create a non-capturing group. Select str from string_entries where str ~ ^(?=.*(?:,|^)foo(?:,|$))(?=. In Postgres regex syntax, parentheses () create a numbered capture group which leads to returning the contained matching results. See the online DB fiddle: CREATE TABLE string_entries (?=.*(?:,|^)bar(?:,|$)) – immediately on the right, there should be any zero or more chars, as many as possible, and then a bar string (either at the start of string or right after a comma and followed with a comma or end of string position)Īssuming these values must appear as whole words you can replace the non-capturing groups with y word boundaries and use where str ~ ^(?=.*yfooy)(?=.*ybary).We can use The PostgreSQL REGEXPMATCHES () function to search all occurrence required string or only the first one, or we can search for at any position. (?=.*(?:,|^)foo(?:,|$)) – immediately on the right, there should be any zero or more chars, as many as possible, and then a foo string (either at the start of string or right after a comma and followed with a comma or end of string position) The PostgreSQL REGEXPMATCHES () function returns no row, one row, or multiple rows as per the pattern defined.Here, it will match strings that contain foo and bar in any order using the ~ (regex matching) operator.
#POSTGRESQL REGEX FULL#
foo% rather than foo then this would work.Īssuming these values must appear as full comma-separated entries, you can use where str ~ ^(?=.*(?:,|^)foo(?:,|$))(?=.*(?:,|^)bar(?:,|$)) This will only work if you need equality comparison. I am inclined to not do this with a regex comparison, but convert the string into an array: select * Sql – Postgresql regex match string with substring
0 kommentar(er)
