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

El índice del blog Firebird21