CODEWITHSUNDEEP
phpmysql
Belgin Fish
Belgin Fish

Reputation: 19857

Mysql Search Treat Periods Like Spaces

well right now I have a bunch of data stored in my db in the format.

this.is.the.name

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.

Upvotes: 1

Views: 401

Answers (4)

Dean Brundage
Dean Brundage

Reputation: 2048

You can do a regular expression search 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.

Upvotes: 0

stivlo
stivlo

Reputation: 85546

Instead of doing the replace on the field, like other answers suggest, you could do the replace on the search term. 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, '.', ' ')

Upvotes: 3

garnertb
garnertb

Reputation: 9594

You can use the REPLACE function.

 SELECT REPLACE('this.is.the.name', '.', ' ');

 --> 'this is the name'

Upvotes: 0

Aurelio De Rosa
Aurelio De Rosa

Reputation: 22172

You can use the replace function of mysql in your WHERE clausole:

replace([field_name],'[string_to_find]','[string_to_replace]');

Upvotes: 2

Related Questions