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
