ignore spaces when searching mysql

Should i use REGEXP to ignore spaces when searching?

[hughie]

Hi All

I've built a search that works but relies on the users to input their query very accurately.

The data they're searching on are serial numbers which come from a big data feed.

Product - Code
10mm Steel Pole - TS 200-10
20mm Steel Pole - TS 200-20
....
...

What happens is people are typing in "TS200-10" and this it's coming up with zero results.

One way i've solved it is to run through the table and strip out the spaces with php and save them in another column, but i'd rather use a cleaner method if it's possible.

Any help would be most appreciated.

Cheers,
hughie

[eelixduppy]

You could use regular expressions [dev.mysql.com] for pattern matching [dev.mysql.com] although I'm not 100 percent familiar with the subject.

Maybe you could check to see if the row contains the search query and then return that row if it does.

Good luck! Sorry I couldn't be of more help.

[phranque]

you could try using the replace function to replace all blanks with nulls in the selected column(s).

SELECT REPLACE(column_name,' ','') AS temp_name FROM table_name WHERE temp_name ...

[hughie]

just the ticket, great stuff, many thanks!