well right now I have a bunch of data stored in my db in the format.
where instead of having a space, there is a period.
I'm just wondering how I could do my mysql search to treat the periods like spaces? Or just have it so if a user searches "this is the" it'll return the entry.Answer1:
Instead of doing the replace on the field, like other answers suggest, you could do the <strong>replace on the search term</strong>. In this way MySQL could still use an index on
field1. Supposing that the periods are always there instead of spaces
SELECT field1, field2 FROM table WHERE field1 = REPLACE('user input', ' ', '.')
If you want to visualise without dots you could do the reverse replace in the
SELECT part too:
SELECT REPLACE(field1, '.', ' '), field2 FROM table WHERE field1 = REPLACE('user input', ' ', '.')
Or you can even consider to update your DB, instead of fighting with it:
UPDATE table SET field1 = REPLACE(field1, '.', ' ')Answer2:
You can use the
replace function of mysql in your
You can use the <a href="http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace" rel="nofollow">REPLACE</a> function.
SELECT REPLACE('this.is.the.name', '.', ' '); --> 'this is the name'Answer4:
You can do a <a href="http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html" rel="nofollow">regular expression search</a> to find entries that match. You will have to do a little pre-processing before you search the database.
Use the underscore character as a single wildcard:
SELECT name FROM table WHERE name LIKE "this_is_the_%"; # returns # - this.is.the.name # - this-is-the-name # - this.is_the_name # - this is the name
Check out the REGEXP search too.