0

In my database I have two forms of the same word. So for example when I search for amor I get amor and āmōr. How can I fetch just āmōr from the database.

  • And your current `COLLATION` is? Take a look at `utf8_bin` and read up on fulltext search in general. Possible Dupe: http://stackoverflow.com/questions/500826/how-to-conduct-an-accent-sensitive-search-in-mysql – ficuscr Nov 01 '16 at 21:12
  • @ficuscr In my case I have the input 'amor' without special characters and I am searching for the form 'āmōr' –  Nov 03 '16 at 15:36

1 Answers1

1

If you don't want to mess around with Collation (for some reason):

SELECT a.text
FROM table a
WHERE a.text = 'amor'
AND BINARY(a.text) = BINARY('amor')

I left the first condition with the plain text so that MySQL can use an index on text, in case that exists. The second condition will make sure you are only getting the exact text (including capitalization) you searched for.

EDIT: Based on comment.

If you do want to get the accented result, you need to use a collation that ignores such accents. The one you need to use depends on the collation of the column..

Try any of the below:

SELECT a.text
FROM table a
WHERE a.text = 'amor'
COLLATE 'utf8_general_ci';

Or: 

SELECT a.text
FROM table a
WHERE a.text = 'amor'
COLLATE 'latin1_general_ci';

EDIT #2: Based on more comments.

To always get the accented version in case there is more than one match:

SELECT a.text
FROM table a
WHERE a.text = 'amor' -- this condition returns both results
ORDER BY LENGTH(BINARY(a.text)) DESC
LIMIT 1
Galz
  • 6,713
  • 4
  • 33
  • 39
  • This would not work in my case because I am not searching the exact input. I am looking for a possibility where I can input the normal "amor" and I get the "āmōr" with special characters. –  Nov 01 '16 at 22:44
  • for 'utf8_general_ci' the query does not work, for 'latin1_general_ci' I still get both results... –  Nov 03 '16 at 11:58
  • @ChrisWinterbottom - I thought you want both results! So you want to search for 'amor', and only get the accented result even though both exist? What's the logic? What would you want to get if there are 3 versions that match? Usually the options are either the exact match or all matches.... – Galz Nov 03 '16 at 15:32
  • The logic behind it would be that for example someone is searching a word in the Dictionary(the db) and he cannot type in the form with the special characters because of his keyboard...and no, in the db there are only two different matches. –  Nov 03 '16 at 18:56
  • @ChrisWinterbottom - but how do you know if they meant "āmōr" and could not type it, or just meant "amor"? Why is the "āmōr" result better? – Galz Nov 03 '16 at 18:57
  • Yes, I know that they meant "āmōr" because in poetry you only need "āmōr". –  Nov 03 '16 at 19:57
  • @ChrisWinterbottom that's a very specific use case though. If you change your query specifically for this, how about other queries with a similar issue, where it's not clear that only one is used in poetry? – Galz Nov 03 '16 at 19:59
  • @ChrisWinterbottom - what is the rule you want in the query? Is it (a) "if user search for 'amor', return āmōr", or (b) "if user searched for a string with no accents, and there's a match with accents, return the match with accents" – Galz Nov 03 '16 at 21:44
  • @ChrisWinterbottom - please see if latest edit helps. – Galz Nov 03 '16 at 21:55
  • For the last edit...how can I search in several fields? –  Dec 04 '16 at 20:52