sort / search / display - columns
Posted by: Dominik Meyer
Date: July 15, 2012 03:04AM

Hello guys,

We have a problem with collations and sorting behavior of mysql.

We have in a table "names" names like this:
Müller, Měyer, Śebastian, Ałan, ..

Now we want find the row "Müller" when we search after Mueller, Müller (not on Muller)
We want find the row "Měyer" when we search after Měyer, Meyer
We want find the row "Ałan" when we search after Ałan, Alan

So ä = ae and ě = e and ł = l.... (also on sorting it must be the same)

We also needs "no sorting words".

For example, we put on the name "Wolfgang Johann {von}"
{-signs to say the system that it is a non sorting word.

Our solution:
we have 3 columns with the same name. "name, name_sort, name,search".
name_search is used for boolean fulltext search. (on save we change ü/ä/ö/ě/ł.. to ue/ae/oe/e/l..), name_sort is used for sorting (we do the same + remove the words in {}-signs), name is only the display column(we romve the {- signs on output)

is there a way to do this without having this 3 colums ?
this (and other tables, like titles) have many rows, so we cant transform before a query.



Edited 1 time(s). Last edit at 07/15/2012 09:06AM by Dominik Meyer.

Options: ReplyQuote


Subject
Views
Written By
Posted
sort / search / display - columns
3677
July 15, 2012 03:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.