Ignoring certain characters with SELECT query

[Ben H]

Hello, I've built a MySQL database to manage product and stock details. Each item has a serial number associated with it, called an OE Number. In our 'stock' table, there is a record for each unique OE Number. The OE Number can contain letters, numbers and other characters, and can be of variable length so the field is in 'varchar' format.

We store the OE Numbers based on the way that the respective manufacturer does. So, for example, a unit manufactured by Hitachi may have the following OE Number:

AB-1234-XY

Whereas a unit produced by Mitsubishi could be formatted as:

1234-ABCD

Each manufacturer has different conventions for the length of the number and the positions of the dashes.

When our customers send us lists of OE numbers they wish to buy or sell, some of them follow a different format wherein there are no dashes -- they could be replaced by spaces (AB 1234 XY), dots (AB.1234.XY) or even nothing at all (AB1234XY). The lists they send us contain thousands of entries, so it would obviously help us enormously to be able to automate the checking of their lists against our own stock levels.

I am completely stuck on how to perform these searches on our MySQL database. I suspect that the best way to conduct these searches would be to strip out all of the dashes, dots and spaces. However, I am unsure of how to go about comparing the search string (which would be 'AB1234XY') to the format that our records are stored in on the 'stock' table ('AB-1234-XY'). Basically I need MySQL to ignore the dashes that are included in the OE Numbers in our stored records when we are conducting these searches.

Unfortunately I cannot simply delete the dashes in our records, as most of our regular clients prefer this format (it is the quickest and easiest to read).

If anybody could offer any suggestions or advice, then I would be extremely grateful.

Many thanks,
Ben