Led Led - 4 years ago 99
SQL Question

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

Answer Source

Use join with like

SELECT cageNumber,name
FROM  A
INNER JOIN B ON A.fur LIKE CONCAT('%', B.fur, '%')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download