MySQL use REGEXP with multiple rows

Question

I have a database with two tables. The first table has a single column which contains regex expressions (there are about 200 rows). The second table has multiple rows and I would like to get all the rows from the second table that matches all the regex from the first table.

For example, if the first table has:

^google.com/orp
^amazon.com/ssw
^stack/ik9

and the second table has

stack/ik9282
msifks0
amazon.com/ssw9a

the result should be

stack/ik9282
amazon.com/ssw9a

I have tried to do this using GROUP_CONCAT and get the regex from my first table as ^google.com/orp|^amazon.com/ssw|^stack/ik9, but GROUP_CONCAT has a maximum length of 1024 and I need more.

Akina · Accepted Answer · 2020-11-13 11:08:39Z

2

SELECT DISTINCT strings.value
FROM strings
JOIN patterns ON strings.value REGEXP patterns.pattern;

fiddle

GROUP_CONCAT has a maximum length of 1024

You may adjust group_concat_max_len variable setting.

answered Nov 13, 2020 at 11:08

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

2 Comments

tatulea Over a year ago

Thanks! How can I modify it such as I will get the rows that are not matching the regex? I actually need a query to get what matches and another one to get what doesn't match.

Gordon Linoff Over a year ago

@tatulea . . . That is not the question you asked here. This answers the question that you did ask. You can modify this answer using left join and where to get the values that don't match.

Collectives™ on Stack Overflow

MySQL use REGEXP with multiple rows

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related