Ignoring white space on MySQL LIKE statement

HI guys,

I have a search page feature on my website (PHP & MySQL 5). At the moment i am using SELECT * FROM table WHERE somefield LIKE '%keywords%'

The problem is the search became really sensitive when it comes to whitespace. Say if i want to search for "Radio A", i have to put "Radio A" for the search to find the record.  When someone put "RadioA" (Without spaces) it wont find any results.

I dont want to explode "Radio A" into "Radio" and "A", as it will return any other "Radio" results which we dont want.

Any hints and tips would be appreciated.

JaZziDAsked:
Who is Participating?
 
hernst42Connect With a Mentor Commented:
Yup error in the example. you need to replace any occurency of \s with [[:space:]] in my example
0
 
steelseth12Commented:
SELECT * FROM table WHERE somefield LIKE '%keywords%' OR somefield LIKE '%trim(keywords)%'
0
 
hernst42Commented:
regular expresion might help in thiscase (if your mysql-version supports it)
http://dev.mysql.com/doc/refman/5.0/en/regexp.html

in your programming langnguage use a function to replace any whitespace with an emoty string, then put between each char a \s*
so if the user enters "radioA"or "radio A" $keywords will be "r\s*a\*sd\s*i\s*o\s*A"
and the sql:

SELECT * FROM table WHERE somefield REGEXP 'r\s*a\*sd\s*i\s*o\s*A'
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
or, replace the whitespace by % before submitting the keyword for the search...
0
 
JaZziDAuthor Commented:
hi guys,

let me try what hernst suggested first
0
 
JaZziDAuthor Commented:
hi hernst... thanks for that, it works great.

angelll: when i tried to replace the whitespace with % it didnt work somehow
0
All Courses

From novice to tech pro — start learning today.