I'm working on an inventory solution written in PHP, and I'm facing an issue.
For each item created in my MySQL table, I'm registering the related product's ID, and it's very own serial number (SN) - amongst other specific data.
Keeping in mind that different products might share an indentical SN, I cannot go for a UNIQUE key on the SN's column.
On another hand, SNs are registerd by hand, and according to the user creating the article, a given SN could be written in different ways : AE00000121, AE 00 00 01 21, AE121, AE 121, ...
To add to the complexity, as I am working with several brands, SNs do not share a format, a lenght, or a standard form.
How can I simply check, upon INSERT (or before), that a given SN does not already exists in DB for a given product ID, whatever the way it was written.
I imagined a SQL query counting entries WHERE id=id and SN LIKE %SN%, but that does not seem strong enough...
Any help would be appreciated to get that query on foot.
Thanks