Mysql searching records containing wildcards

Question

I have a table with tens of thousands of VIN numbers. Many of them look along the lines of this:
6MMTL#A423T######
WVWZZZ3BZ1?######
MPATFS27H??######
SCA2D680?7UH#####
SAJAC871?68H06###

The # represents a digit and the ? a letter (A-Z).

I want to search for the following: 6MMTL8A423T000000.

I am struggling to work out the logic. Should I use a function? Should I use mysql regex?

Sorry to be confusing. I mean the table contains the actual "6MMTL#A423T######" and "6MMTL#B423T######", for example. So I input 6MMTL8A423T000000 and that should match "6MMTL#A423T######" and not "6MMTL#B423T######". The output of the query should be "6MMTL#A423T######" exactly as it is written (without quotation marks). — carbroker, Nov 17 '12 at 01:13
To clarify further (I hope). Each record contains the make and model of car plus a pattern that show how VIN numbers are constructed for this make/model. I come along with a specific VIN for a specific car and I want to know what make/model is a match. — carbroker, Nov 17 '12 at 01:19

score 1 · Accepted Answer · answered Nov 17 '12 at 17:21

A regular expression match would be a good way to approach this problem. What you need to do is convert the vin expressions into valid regular expressions that represent the logic you've indicated. Here's a simple way to do that:

replace(replace(vin,'#','[0-9]'),'?','[A-Z]')

This would convert 6MMTL#A423T###### into 6MMTL[0-9]A423T[0-9][0-9][0-9][0-9][0-9][0-9]. Now using this converted format, do a regular expression match query:

select vin
from vins
where '6MMTL8A423T000000' regexp replace(replace(vin,'#','[0-9]'),'?','[A-Z]')

Sample Output: 6MMTL#A423T######

Demo: http://www.sqlfiddle.com/#!2/ee4de/4

Mysql searching records containing wildcards

1 Answers1