Matching field on alphanumerics only

Suppose I have a purely alphanumeric string ("abc123"开发者_运维技巧), and I want to see if it's in a database column, with one caveat-- I want all nonalphanumeric characters to be ignored in the comparison. So "abc~!@#$%^&*()123" would match, for example.

Is there a simple way to do this in MySQL, preferably without defining any functions? I was thinking it might be possible with REGEXP comparison.

If your table is big, then performing any function on a column is going to result in a table scan. What about creating another column with the alnum characters only, and then doing a straight up comparison against that?

Figured it out with this regular expression..

$regexp = implode('[^0-9a-zA-Z]*', str_split(remove_nonalphanums($word)));

Then I do The_Column REGEXP $regexp

If this is something that you will be doing on a regular basis, then add a new column for your 'normalized' string and search on that column. Doing a regex match in the database is convenient but highly inefficient. Better to just normalize your search on the client and use a little extra db space.