MySQL search to ignore hyphens

Question

How can I search for "1-800-flowers" by "1800flowers" in MySQL?

I have the data "1-800-flowers", but I want to find it by "1800flowers".

Answer 1

You're probably best off creating a second column that you fill with 1800flowers (replacing all characters you want to ignore) and searching that. That way, you can make full use of indexing.

A quick way to convert all existing data would be

UPDATE table SET columnname_without_hyphens = REPLACE(columnname, "-", "");

Answer 2

If your problem is just ignoring hyphens, I may suggest using REPLACE to eliminate them, like this:

SELECT ... WHERE REPLACE(column, '-', '') ...

Otherwise, if you're looking for strings that "sound alike", you may want to have a look at the SOUNDEX function.