4

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".

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Mahfuz
  • 467
  • 6
  • 11
  • You can remove your hyphens before your data being passed to mysql like with php `str_replace($search_str, '-', '');` –  Oct 18 '11 at 14:54

3 Answers3

6

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, "-", "");
Pekka
  • 442,112
  • 142
  • 972
  • 1,088
3

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.

ChrisJ
  • 5,161
  • 25
  • 20
1

The use of the replace function will kill any ability to use an index on the column, but:

select *
    from YourTable
    where replace(YourColumn, '-', '') = '1800flowers'
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235