I have a problem with a field that appears on a number of my tables.
The field is the Vehicle Identification Number. Every vehicle has one and it
uniquely identifies that vehicle.
Traditionally this was a 11 character string but a number of years ago was
extended to 17 characters by adding a 6 character prefix.
The problem that I have is that these VIN numbers are provided by a number of
data systems including manufacturer feeds, logistics companies as well as
internal systems. Some use the full 17 character string while others only use
the last 11.
On top of this, my users are used to only having to type the last 6 characters
for speed and usability reasons.
However, it means that every time I'm trying to connect various tables up
using foreign keys or doing searches I have to make allowences for this which
means I'm using things like substring, like, regex etc. all of which are very
slow.
Can anyone suggest a better / more efficient way of handling these.
Gary