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?
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######