CODEWITHSUNDEEP
mysqlsqlregex
Led
Led

Reputation: 517

Mysql treat column value as a regex for select

is it possible to query a result depending on the regex pattern of a column?

assuming i have 2 tables

table A
legs     fur                name
4        red                 cat
4        blue || spots       dog
1                            dolphin
1        black               shark
1        yellow              shark
1        white|| black       whale
2        [0-9]{4}-[0-9]{2}   exp1
2        [0-9]{4}            expA-1


table B
cageNumber   weight      legs     fur
192910       26          4        red
332192       12          1        black
119199       32          4        blue
111000       19          4        spots
192991       11          4        green
000001       14          2        0913-11        
000002       11          2        1102      
000003       16          2

what I need to do is to have a select statement which describes cage number and name depending on fur color

cageNumber name
192910     cat
332192     shark
111000     dog
119199     dog
192991     null
000001     exp1
000002     expA-1
000003     null

Upvotes: 2

Views: 5107

Answers (4)

Nishant Nair
Nishant Nair

Reputation: 2007

Use join with like 

SELECT b.id, a.initiatorWallet FROM ledTest a
INNER JOIN ledTestB b ON a.transtype = b.transType
WHERE b.wallet RLIKE ( a.regex)

Upvotes: -1

Rick James
Rick James

Reputation: 142298

Change blue || spots to blue|spots to make it a correct regular expression.

Then this should work:

ON B.fur REGEXP CONCAT('^(', A.fur, ')$')

I added ^ and $ in order to anchor to the ends. That way, blue will match, but blueblood will not.

I added ( and ) so that blue|sports would match only blue and sports, not blueblood and teamsports. Think about what happens with ^blue|sports$ -- that means "start with blue or end with sports".

Upvotes: 3

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

SELECT B.cageNumber, A.name FROM A JOIN B ON B.fur LIKE A.fur;

Upvotes: 0

Sascha A.
Sascha A.

Reputation: 4616

Yes you can do this, look at my little example. It delivers only expertId=1, contingentId=59 and description starts with ## followed by a number.

select * from entry where expert_id=1 and contingent_id=59 and (description REGEXP '^##[0-9]');

Upvotes: 0

Related Questions