Habrás notado que en las columnas de tipo carácter a veces aparece la palabra COLLATE.
¿Para qué sirve COLLATE?
Para decirle al Firebird cual ordenamiento debe utilizar en las columnas de tipo carácter. ¿Por qué eso? porque el ordenamiento depende del idioma. Por ejemplo en el idioma ruso hay muchos caracteres que no existen en el idioma castellano:
http://es.wikipedia.org/wiki/Alfabeto_ruso
entonces, como el Firebird se usa por personas que hablan en muchos idiomas y cada uno de ellos quiere ver sus informes correctamente ordenados según su propio idioma es que se le debe indicar como ordenar las letras. Un ordenamiento óptimo en castellano puede ser un desastre en holandés, polaco, ruso, etc. Y viceversa: en ruso puede estar perfecto pero en castellano quedar horrible.
¿Cuál es la solución a este problema?
Indicar el ordenamiento que se desea emplear (o sea, el COLLATE).
¿Cuándo se debe usar COLLATE?
Cuando la columna será ordenada (cláusula ORDER BY de un SELECT) o buscada (cláusula WHERE de un SELECT) y convertida a mayúsculas (función UPPER())
¿Qué significan las letras?
Las dos primeras indican el idioma. Las dos últimas el país o región. ¿Por qué? porque a veces hay diferencias en como se usa un idioma. Por ejemplo el francés hablado en Francia es distinto al francés hablado en Canadá. Y el inglés hablado en Inglaterra es distinto al inglés hablado en los Estados Unidos. Y el portugués de Portugal es distinto al portugués de Brasil.
FR_CA significa: idioma francés, tal como se lo habla en Canadá
FR_FR significa: idioma francés, tal como se lo habla en Francia
EN_UK significa: idioma inglés, tal como se lo habla en Inglaterra
EN_US significa: idioma inglés, tal como se lo habla en los Estados Unidos
PT_BR significa: idioma portugués, tal como se lo habla en Brasil
PT_PT significa: idioma portugués, tal como se lo habla en Portugal
¿Cuál COLLATE se debe usar con el idioma castellano?
El COLLATE adecuado es el ES_ES (español, tal como se lo habla en España)
Afortunadamente para nosotros el ordenamiento es igual en todos los países de habla castellana, así que no debemos preocuparnos por las dos últimas letras. Pero sí debemos recordar de usar COLLATE ES_ES en todas las columnas que ordenarán o se buscarán. Si no lo hacemos, entonces el orden podría ser incorrecto o podríamos no encontrar lo que estamos buscando. ¿Por qué eso? Porque el Firebird no tiene un COLLATE por defecto, por lo tanto siempre debemos indicarle uno si queremos asegurar que obtendremos los resultados correctos.
¿Qué significan las letras CI_AI?
A partir del Firebird 2.0 a algunos COLLATE (el ES_ES incluido) se les puede agregar CI_AI.
CI significa «case insensitive». O en otras palabras, que no distinga entre mayúsculas y minúsculas.
AI significa «accent insensitive». O en otras palabras, que no distinga entre vocales acentuadas y no acentuadas.
Por lo tanto, si elegimos el COLLATE ES_ES_CI_AI le estamos diciendo al Firebird: «ordena las columnas según el alfabeto español, tal como se lo usa en España, sin diferenciar entre mayúsculas y minúsculas, y sin diferenciar entre vocales acentuadas y no acentuadas».
Para más información sobre el uso de COLLATE ES_ES_CI_AI puedes leer este artículo:
Consultando sin importar mayúsculas ni acentos
Ejemplo 1
CREATE TABLE ALUMNOS ALU_NOMBRE VARCHAR(25) COLLATE ES_ES, ALU_APELLD VARCHAR(25) COLLATE ES_ES, ...
Aquí le estamos diciendo al Firebird: «usa el ordenamiento del idioma castellano, tal como se lo usa en España». Y como el ordenamiento es el mismo en España y en todos los demás países de habla castellana, ya está bien así.
Ejemplo 2
SELECT ALU_NOMBRE, ALU_APELLD FROM ALUMNOS ORDER BY ALU_APELLD COLLATE ES_ES, ALU_NOMBRE COLLATE ES_ES
Aquí le estamos diciendo al Firebird que nos muestre las columnas ALU_NOMBRE y ALU_APELLD, ordenadas según las reglas del idioma castellano.
Ejemplo 3
SELECT ALU_NOMBRE, ALU_APELLD FROM ALUMNOS WHERE ALU_APELLD COLLATE ES_ES = :tcApellido
Aquí, hacemos una búsqueda en la columna ALU_APELLD, usando las reglas del idioma castellano
Ejemplo 4
SELECT ALU_NOMBRE, ALU_APELLD FROM ALUMNOS WHERE UPPER(ALU_APELLD COLLATE ES_ES_CI_AI) = 'GARCIA'
Aquí, dentro de la función UPPER() se especificó el COLLATE que queremos usar. Eso es importante a recordar: para que la función UPPER() trabaje correctamente debemos especificar el COLLATE dentro suyo. Y como en este ejemplo usamos CI_AI, obtendremos ‘GARCIA’, ‘GARCÍA’, ‘Garcia’, ‘García’, ‘garcia’, etc.
Conclusión:
Los idiomas que hablamos los seres humanos tienen muchos alfabetos y muchas formas de ordenar esos alfabetos. Como el Firebird está pensado para ser usado por personas de todos los países del mundo entonces debe tener alguna forma de ordenar en forma correcta a las columnas de tipo carácter. Esa forma es mediante la cláusula COLLATE.
Al usar COLLATE nos aseguramos de que siempre las columnas de tipo carácter estarán ordenadas como queremos nosotros y que las búsquedas en esas columnas encontrarán lo que se está buscando.
Si no usamos COLLATE entonces …., no se garantiza que el resultado obtenido sea el correcto.
Artículos relacionados:
Consultando sin importar mayúsculas ni acentos
Roy Salas Benavides
Mar 10, 2014 @ 12:22:29
Walter, si utilizamos el collate en la creación de la tabla, ¿ es necesario especificarlo también en la clausula del order by de la consulta ?
wrov
Mar 10, 2014 @ 12:28:32
No, ya no, porque en ese caso el Firebird ya sabe cual ordenamiento deseas utilizar.
Saludos.
Walter.