Hay que tener en cuenta que cada alfabetización es diferente.

Por ejemplo, en noruego hay tres caracteres que no existen en el alfabeto europeo tradicional. En su abecedario, estas tres letras van al final y por ese orden: Æ, Ø, Å

En el catalán y español, la Ç y la Ñ se situan justo detrás de la C y la N.

Pero, aunque sean muy similares, en catalán la CH no existe como letra, por lo tanto, en español o en catalán deberán tener diferentes posiciones.

SQL> select c1 nombre
  2	 from (values
  3	 ('Maa'), ('Mab'), ('Maca'), ('Maco'), ('Macu'), ('Mach'), ('Mad')
  4	 )
  5	 t ( c1 )
  6	order by c1 collate spanish;

NOMBRE
----------
Maa
Mab
Maca
Maco
Macu
Mach   -- La CH va detrás de C en español
Mad

7 rows selected.

SQL> select c1 nombre
  2	 from (values
  3	 ('Maa'), ('Mab'), ('Maca'), ('Maco'), ('Macu'), ('Mach'), ('Mad')
  4	 )
  5	 t ( c1 )
  6	order by c1 collate catalan;

NOMBRE
----------
Maa
Mab
Maca
Mach   -- La CH no se considera letra del alfabeto y se ordena entre G e I.
Maco
Macu
Mad

7 rows selected.

No sólo eso, sino que Oracle permite que esas ordenaciones sean transparentes a mayúsculas y acentos, basándose en cómo se posicionan éstas en el alfabeto.

Dicho esto, no es necesario utilizar funciones de conversión de carácteres como UPPER(), LOWER(), TRANSLATE() o REPLACE() para tener un conjunto de filas ordenadas según español, catalán o inglés.

Vamos con un ejemplo.

Supongamos esta lista de valores, que incluye mayúsculas, minúsculas, letras con tilde (abiertas y cerradas), ce con cedilla (ç) y eñes (ñ):

‘Maçia’, ‘Mañia’, ‘Mabia’, ‘Macia’, ‘Mania’, ‘MAria’, ‘Maria’, ‘MÁria’, ‘MÉria’, ‘MÍria’, ‘MÓria’, ‘MÚria’, ‘maria’, ‘mária’, ‘méria’, ‘míria’, ‘mória’, ‘múria’, ‘Maxia’, ‘MEria’, ‘Meria’, ‘meria’, ‘MIria’, ‘Miria’, ‘miria’, ‘MOria’, ‘Moria’, ‘moria’, ‘MUria’, ‘Muria’, ‘muria’

Ordenar con LOWER(nombre) no va a funcionar bien. Tampoco sirve utilizar REGEXP o funciones por el estilo.

No hace falta.

En el siguiente ejemplo, sin siquiera necesidad de usar una tabla física, generamos una tabla dinámica a partir de la lista de valores. Con COLLATE y el sistema de ordenación SPANISH_AI indicaremos que queremos una ordenación en base a las reglas de español sin sensibilidad a considerar acentos, mayúsculas o minúsculas.

select c1 nombre
 from (values 
    ('Maçia'), ('Mañia'), ('Mabia'), ('Macia'), ('Mania'), ('MAria'), ('Maria'),
    ('MÁria'), ('MÉria'), ('MÍria'), ('MÓria'), ('MÚria'), ('maria'), ('mária'),
    ('méria'), ('míria'), ('mória'), ('múria'), ('Maxia'), ('MEria'), ('Meria'),
    ('meria'), ('MIria'), ('Miria'), ('miria'), ('MOria'), ('Moria'), ('moria'),
    ('MUria'), ('Muria'), ('muria')
    )    t ( c1 ) 
order by c1 collate spanish_ai;
	
NOMBRE
----------
Mabia
Maçia   -- La ubicación correcta de Ç es entre B y C.
Macia
Mania
Mañia   -- La ubicación correcta de Ñ justo después de N.
Maria
MAria
Mária   -- A, a, Á, á, se consideran al mismo nivel
mária
maria
Maxia
meria
Meria
méria
MÉria
MEria
MÍria
míria
miria
Miria
MIria
MÓria
moria
MOria
mória
Moria
muria
MÚria
múria
Muria
MUria

31 rows selected.

Si quieres que un carácter de se ordene una forma especial, mi recomendación es que crees una ordenación específica o modifiques una de las estándares para el idioma que desees.

Para ello tienes la utilidad Oracle Locale Builder.

[oracle@localhost ~]$ lbuilder

Esta utilidad te permite cargar los ficheros de definiciones locales, y te permite establecer cómo quieres que se comporte según el lenguaje, territorio, juego de carácteres y ordenación lingüística.

Por ejemplo, voy a crear una ordenación personalizada del español llamada SPANISH_AMI por ser «a mi modo». En ella quiero que los números cero y tres se ordenen junto a la letra O y la E.

Esto se consigue modificando la “ordenación principal” y la “ordenación secundaria” para que se ubiquen en la posición deseada.

Guardo los cambios en el directorio de NLS por defecto de Oracle, que es $ORACLE_HOME/nls/data , y las ordenaciones ahora pueden acceder a esta nueva ordenación a través de los nuevos ficheros creados con las reglas que hemos personalizado.

SQL> select c1 nombre
2 from (values
3 ('Maa'), ('Mae'), ('Mai'), ('Mao'), ('Mau'), ('Ma0'), ('Ma1'),
4 ('Ma2'), ('Ma3'), ('Ma4'), ('Ma5') )
5 t ( c1 )
6 order by c1 collate spanish_ami;

NOM
---
Maa
Mae
Ma3 --> El 3 se alínea ordenado con la letra E
Mai
Mao
Ma0 --> El 0 se alínea ordenado con la letra O
Mau
Ma1
Ma2
Ma4
Ma5

11 rows selected.