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,030
  • 21
  • 100
  • 124
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
  • 431,103
  • 135
  • 960
  • 1,075
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.

ChrisJ
  • 5,041
  • 23
  • 19
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
  • 128,689
  • 18
  • 228
  • 231