Search code examples
mysqlalphabetical-sort

SQL Modify alfabetical order, including composite characters

I want to order by a modified alphabetical order, including ‘letters’ that are made of 2 or 3 characters (i.e.: a, ä, ch, chh, ch’, i, ï, j, k, kh, k’, l, ll, m, n, ñ, p, ph, p’, q, qh, q’, r, s, t, th, t’, u, ü, w, x, y, ¨ this is a real alphabet from Aymara language), using this criteria I need to make, for example, the following words ordering, small part of a dictionary

NORMAL ORDER DESIRED ORDER Yaq’a Yaqana Yaq’araña Yäqata Yaq’asiwi Yaqiña Yaqana Yaqulla Yäqata Yaqha Yaqha Yaqhipa Yaqhipa Yaq’a Yaqiña Yaq’araña Yaqulla Yaq’asiwi

The composite letters can be located in any place of the word.

Working in MySQL

Thanks in advance

Solution

  • This was a problem of Collation, that is the definition of how to order the words for a given language within a character set

    If the language already have a collation definition in mysql, the instruction COLLATE will call the collation definition, and the query will be presented using it Examples of Character Set and Collation Assignment

    If there is not an appropiate definition, we can add a collation definition that fit our needs Adding a Collation to a Character Set