Postgresql, regex pattern match in "where"

Question

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?

you shouldn't be storing delimited values like that to begin with. — user330315
– user330315, Commented Feb 23, 2022 at 12:52
so you mean all substrings should be stored separately into cells instead of being combined into one cell? or any other solution? — brewphone
– brewphone, Commented Feb 23, 2022 at 13:34
Each value should most probably be a row in a separate table. That's a classical example of a one-to-many relationship — user330315
– user330315, Commented Feb 23, 2022 at 14:20

user330315 · Accepted Answer · 2022-02-23 12:52:16Z

1

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, '|'))

answered Feb 23, 2022 at 12:52

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql, regex pattern match in "where"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related