Chaining REGEXP

[AnthonySterling]

Good afternoon all,

I would like to somehow create an extensible query to 'chain' together a selection of regular expressions.

My SQL skills are few and feeble, so I'd love some help.

I currently have no schema built, so the world (or rather database) is your oyster.

I was looking to have a table containing the data:-

Data Table

id|data

Pattern Table
and a table contains patterns:-

id|name|pattern

I would like the query to return the id field of the row which matches all the chained queries.

Initially, I thought of this:-

SELECT id
FROM DataTable
WHERE DataTable.data
REGEXP (
    SELECT pattern
    FROM PatternTable
    WHERE name = 'numeric'
);

Which doesn't really work.

Each REGEXP call should pass on the value it matches to the next; eventually, if all matches, well, match, it would return the row id.

Any ideas?

Many thanks,

[BrandonK]

Instead of doing a subquery, can you do a conditional join?

SELECT p.postal_code
     , p.city
  FROM postal_codes p
INNER
  JOIN `regexp` r_city
    ON r_city.name = 'city'
 WHERE p.city REGEXP r_city.pattern

Would return all postal codes that match a given pattern on the city.