Postgresql regex match string with substring [duplicate]

Question

I have a string foo,bar what i want to do is to form a query matching both of them (can have gap between them)

for example it should be matching against entries like

aaa,bbbb,foo,zzz,bar
bar,ccc,ddddd,foo,iiiii

but not

aaa,eeeee,foo,rrrrrr,hhhhhh
ooooo,ii,sssss,bar,xxxx

the column that i'm trying to matching against is of type text

i tried doing it like this

select * from string_entries where str similar to '%(?=.*foo)(?=.*bar)%';

but i'm getting this error

ERROR:  invalid regular expression: quantifier operand invalid
SQL state: 2201B

select str from string_entries where str similar to '%foo%' AND str similar to '%bar%'; — Santhosh
– Santhosh, Commented Oct 29, 2021 at 12:51

user330315 · Accepted Answer · 2021-10-29 13:07:18Z

1

I am inclined to not do this with a regex comparison, but convert the string into an array:

select *
from string_entries 
where string_to_array(str, ',') @> array['foo', 'bar']

This will only work if you need equality comparison. If you wanted to also match using e.g. foo% rather than foo then this would work.

answered Oct 29, 2021 at 13:07

user330315

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

2 Comments

Ryszard Czech Over a year ago

Hi, can I ask what where string_to_array(str, ',') @> array['foo', 'bar'] means?

user330315 Over a year ago

@RyszardCzech: string_to_array converts the string to an array (hence the name). The @> operator then tests of the array on the right side is contained in the array on the left side.

Wiktor Stribiżew · Accepted Answer · 2021-10-29 12:57:36Z

1

Assuming these values must appear as full comma-separated entries, you can use

where str ~ '^(?=.*(?:,|^)foo(?:,|$))(?=.*(?:,|^)bar(?:,|$))'

Here, it will match strings that contain foo and bar in any order using the ~ (regex matching) operator. Details:

^ - start of string
(?=.*(?:,|^)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)
(?=.*(?:,|^)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)

Assuming these values must appear as whole words you can replace the non-capturing groups with \y word boundaries and use

where str ~ '^(?=.*\yfoo\y)(?=.*\ybar\y)'

See the online DB fiddle:

CREATE TABLE string_entries
    (str character varying)
;

INSERT INTO string_entries
    (str)
VALUES
    ('aaa,bbbb,foo,zzz,bar'),
    ('bar,ccc,ddddd,foo,iiiii'),
    ('aaa,eeeee,foo,rrrrrr,hhhhhh'),
    ('ooooo,ii,sssss,bar,xxxx')
;

select str from string_entries where str ~ '^(?=.*(?:,|^)foo(?:,|$))(?=.*(?:,|^)bar(?:,|$))';

edited Oct 29, 2021 at 12:57

answered Oct 29, 2021 at 12:48

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

3 Comments

ceving Over a year ago

This ignores word boundaries. How about aaa,foobar,bbb?

Wiktor Stribiżew Over a year ago

@ceving Fair enough, I focused on fixing '%(?=.*foo)(?=.*bar)%'. I fixed the solution to account for comma-separated values.

Wiktor Stribiżew Over a year ago

@ceving I think the two solutions should suffice.

Collectives™ on Stack Overflow

Postgresql regex match string with substring [duplicate]

2 Answers 2

2 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Linked

Related