Advanced MySQL pattern matching (beyond LIKE...%)

Question

This is the example of my current MySQL my_table...

 id          name       code
 1           111        XXXX123456XXXXXXXXXXXXXX
 2           222        XXXX133456XXXXXXX5XXXXXX
 3           333        XXXX123454XXX11XXXXXXABC

Code is a 24 character hexadecimal value where X is the wildcard.

I need to write a query that will return the NAME based on the CODE without a wildcard value X. The given CODE value will be exact but it should compare the string in place, X could match any character.

For example:

SELECT name FROM my_table where code = '012312345611111111111111';

 name
 111

SELECT name FROM my_table where code = '000013345622222225123456';

 name
 222

SELECT name FROM my_table where code = '000123454ABC11234567FABC';

 name
 333

Answer 1

You can use like for this. Are you aware of the _ wildcard?

select t.*
from t
where @YourCode like replace(t.code, 'X', '_');

Of course, you can use regular expressions too. The regular expression would be: concat('^', replace(t.code, 'X', '.'), '$').