Re: remove of special characters

[Jason Collison]

I would create a second (denormalized) column for the product code which is purely numeric, and of course create an index on that column. Easily done via a one-time job in PHP or C or whatever. And if the product code is always constant-width (and numerically unique), then there's no reason to even keep the original column with the slash and decimal.

This is really the only good solution, given what you asked, because of the way the data was originally stored.

Another quick-and-dirty hack you can do if your data allows it, but I'm sure you already considered this: Is the format of the product code always xxx.xx after the slash? If so, just have your PHP app "fix" the product code before doing the query. Even if there are 5 different possibilities (xx.xxx, x.xxxx, etc.) you're still in better shape than using a LIKE and/or string function hacks. My vote still goes for the second numeric column though.