CODEWITHSUNDEEP
mysqlsqlregex
tatulea
tatulea

Reputation: 113

MySQL use REGEXP with multiple rows

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.

Upvotes: 0

Views: 236

Answers (1)

Akina
Akina

Reputation: 42661

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.

Upvotes: 2

Related Questions