One table column is a string that contains multiple substrings separated by delimiter character the pipe char (|), like this "aa-a|aa-a-a|a-a|aa", the delimiter character cannot be the leading and ending char of the column string. And the match check is in "where", when match then the row is selected. Actually it's a search, pass in substring such as "aa-a" and search for all rows that has the "aa-a" as a full substring, the "aa-a-a" should not be a match. Another case also need to be considered when there is only one substring and no delimiter. Something like this:

Select * from tb where REGEX_FUNC(tb.col_1, "pattern")>0

in which the "pattern" might be like "^aa-a$" (1) what the REGEX_FUNC should be, need to create my own? (2) what the "pattern" should be?

CodePudding user response：

No need for a regex match.

You can convert the delimited value into an array, then check the array if it matches your comparison value:

where 'aa-a' = any(string_to_array(col1, '|'))