CODEWITHSUNDEEP
sqlregexdatabase
Pete
Pete

Reputation: 113

Match a Query to a Regular Expression in SQL?

I'm trying to find a way to match a query to a regular expression in a database. As far as I can tell (although I'm no expert), while most DBMS like MySQL have a regex option for searching, you can only do something like:

Find all rows in Column 1 that match the regex in my query.

What I want to be able to do is the opposite, i.e.:

Find all rows in Column 1 such that the regex in Column 1 matches my query.

Simple example - say I had a database structured like so:

+----------+-----------+  
| Column 1 |  Column 2 |
+----------+-----------+
|  [a-z]+  |  whatever |
+----------+-----------+
|  [\w]+   |  whatever |
+----------+-----------+
|  [0-9]+  |  whatever |
+----------+-----------+

So if I queried "dog", I would want it to return the rows with [a-z]+ and [\w]+, and if I queried 123, it would return the row with [0-9]+.

If you know of a way to do this in SQL, a short SELECT example or a link with an example would be much appreciated.

Upvotes: 5

Views: 2523

Answers (2)

kravietz
kravietz

Reputation: 11285

In PostgreSQL it would be:

SELECT * FROM table WHERE 'dog' ~ "Column 1";

Upvotes: 1

Ivan Nevostruev
Ivan Nevostruev

Reputation: 28713

For MySQL (and may be other databases too):

SELECT * FROM table WHERE "dog" RLIKE(`Column 1`)

Upvotes: 4

Related Questions