2996

Mysql Search Treat Periods Like Spaces

Question:

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.

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 WHERE clausole:

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

Answer3:

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.

Recommend

  • How to make an elasticsearch query that filters on the maximum value of a field?
  • ValidationResult Returned From IValidatableObject.Validate Is Not Localized
  • What is the JSF behaviour, if you bind the same backing bean property to two input fields in the sam
  • Cassandra: What is a subcolumn
  • How can I count unique terms in a plaintext file case-insensitively?
  • distinct values from multiple fields within one table ORACLE SQL
  • Unix Network Programming Clarification
  • Django model for a Postgres view
  • how to set variables in a php include file?
  • Image map in Flex
  • Redshift Querying: error xx000 disk full redshift
  • MYSQ & MVC3 SQL connection error \\ ProviderManifestToken but I am using MySQL
  • SAXReader not re-ecape characters
  • Insert new calendar with SyncAdapter- Calendar API Android
  • Rails Find when some params will be blank
  • How to define and use opencv mat of user type
  • Moving mysql files across servers
  • Allowing both email and username for authentication
  • Checking free space on FTP server
  • Extracting HTML between tags
  • Retrieving value from sql ExecuteScalar()
  • Deselecting radio buttons while keeping the View Model in synch
  • Submit form in a displaytag pagination
  • How to check if every primary key value is being referenced as foreign key in another table
  • vba code to select only visible cells in specific column except heading
  • How can I estimate amount of memory left with calling System.gc()?
  • Delete MySQLi record without showing the id in the URL
  • What do the 'size' numbers mean in the windbg !heap output?
  • R: gsub and capture
  • AT Commands to Send SMS not working in Windows 8.1
  • Unanticipated behavior
  • Comma separated Values
  • KeystoneJS: Relationships in Admin UI not updating
  • Hits per day in Google Big Query
  • Trying to get generic when generic is not available
  • Checking variable from a different class in C#
  • Django query for large number of relationships
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • How can I use `wmic` in a Windows PE script?
  • How to push additional view controllers onto NavigationController but keep the TabBar?