Intercalación

Importante

Esta característica está en versión preliminar pública.

Se aplica a: Databricks SQL Databricks Runtime 16.1 y versiones posteriores

Una intercalación es un conjunto de reglas que determina cómo se realizan las comparaciones de cadenas. Las intercalaciones se usan para comparar cadenas de una manera que no distingue mayúsculas de minúsculas, que no distinguen acentos ni espacios iniciales o finales, o para ordenar cadenas en un orden específico con reconocimiento del lenguaje.

Las cadenas de Azure Databricks se representan con caracteres Unicode con codificación UTF-8. De manera predeterminada, Azure Databricks compara cadenas por su representación UTF8 binaria. Esto se conoce como intercalación de UTF8_BINARY. Las comparaciones de UTF8_BINARY son rápidas y adecuadas en muchos casos, pero no necesariamente en todas las aplicaciones, especialmente aquellas que requieren comparaciones u ordenaciones que dependen del idioma.

Además de las comparaciones que dependen del idioma, un caso de uso común es requerir que no distingan mayúsculas de minúsculas. Azure Databricks tiene la intercalación UTF8_LCASE específicamente para este propósito. Convierte las cadenas en minúsculas antes de compararlas utilizando la intercalación rápida UTF8_BINARY.

Para las comparaciones con reconocimiento del idioma, Azure Databricks emplea las siguientes tecnologías:

• Biblioteca Componentes Internacionales para Unicode (ICU) para calcular la intercalación
• Tablas Repositorio de datos de configuración regional común (CLDR) para una intercalación específica basada en la configuración regional.
• Lenguaje de marcado de datos locales de Unicode (LDML) para codificar las intercalaciones internamente.

Estas tecnologías están encapsuladas en un conjunto de intercalaciones con nombre que se pueden utilizar en instrucciones SQL.

Nombres de intercalación

Dado que la identificación de intercalaciones por sus especificaciones LDML puede ser compleja y difícil de leer, Azure Databricks tiene un conjunto de intercalaciones del sistema con nombre que son más fáciles de usar.

Sintaxis

{ UTF8_BINARY |
  UTF8_LCASE |
  { UNICODE | locale } [ _ modifier [...] ] }

locale
  language_code [ _ script_code ] [ _ country_code ]

modifier
  { CS | CI | AS | AI | RTRIM }

• UTF8_BINARY

  Intercalación binaria metaregional que compara cadenas byte por byte en función de la representación de bytes UTF-8. UTF8_BINARY es la collation predeterminada y la más ligera para la comparación de cadenas en Azure Databricks.

  En esta intercalación "A" (x"65") < "B" (x"66") <... < "Z" (x"90").

  Sin embargo, "Z" (x"90") < "a" (x"97") y "A" (x"65") <> "a" (x"97").

  Además, los caracteres como 'Ä' (x'C384') son superiores a 'Z' y 'z' en esta ordenación.

• UTF8_LCASE

  Una intercalación metaregional ligera sin distinción entre mayúsculas y minúsculas que compara cadenas usando su representación de bytes UTF-8 después de convertir las cadenas a minúsculas.

  UTF8_LCASE es la intercalación que se usa para los identificadores en Azure Databricks.

  Por ejemplo:

  ORDER BY col COLLATE UTF8_LCASE
  

  equivale a

  ORDER BY LOWER(col) COLLATE UTF8_BINARY
  

• UNICODE

  Configuración regional raíz de la ICU.

  Esta intercalación, conocida en CLDR como configuración regional "raiz" (especificación LDML: "und-u") impone un orden independiente del lenguaje, que intenta ser intuitivo en general. En esta intercalación, los caracteres similares (like) se agrupan. Por ejemplo: "a" < "A" < "Ä" < "b". "A" no se considera equivalente a "a". Por lo tanto, la intercalación distingue entre mayúsculas y minúsculas. "a" no se considera equivalente a "ä". Por lo tanto, la intercalación distingue los acentos.

• locale

  Una intercalación con configuración regional que depende del idioma basada en las tablas CLDR.

  La configuración regional se especifica como un código de lenguaje, un código de script opcional y un código de país opcional. locale no distingue mayúsculas de minúsculas.

  • language_code: código de idioma de dos letras ISO 639-1.
  • script_code: código de script ISO 15924 de cuatro letras.
  • country_code: Un código de país de tres letras ISO 3166-1 alpha-3.

• modificador

  Especifica el comportamiento de intercalación con respecto a la distinción entre mayúsculas y minúsculas y los acentos.

  • CS: distingue mayúsculas de minúsculas. Comportamiento predeterminado.
  • CI: no distingue mayúsculas de minúsculas.
  • AS: sensible a los acentos. Comportamiento predeterminado.
  • AI: distingue los acentos.

  Se aplica a: Databricks Runtime 16.2 y versiones posteriores

  • RTRIM: no distingue entre espacios en blanco. Elimina los espacios en blanco finales ('u0020') antes de la comparación.

  Se aplica a: Databricks Runtime 16.2 y versiones posteriores

  Puede especificar RTRIM, ya sea CS o CI, y AS o AI como máximo una vez y en cualquier orden. Los propios modificadores no distinguen mayúsculas de minúsculas.

Al procesar una intercalación, Azure Databricks normaliza los nombres de intercalación quitando los valores predeterminados. Por ejemplo, SR_CYR_SRN_CS_AS se normaliza en SR.

Para obtener una lista de intercalaciones admitidas, consulte Intercalaciones admitidas.

Ejemplos

-- You can fully qualify collations, and case doesn't matter.
system.builtin.unicode

-- Since all collations are system defined you don't need to qualify them
unicode

-- Using 2-letter language code only for german collation
DE

-- Using 2-letter language code and 3-letter country code for french-canadian collation
-- It is common to use lower case 2-letter language codes and upper case 3-letter country codes
-- But collation names are case insensitive
fr_CAN

-- Using 2-letter language code and 4-letter script code and 3-letter country code for traditional chinese in Macao
zh_Hant_MAC

-- Using a 2 letter german language code and 2 modifiers for case insensitive and accent insensitive
-- So 'Ä', 'A', and 'a' are all considered equal
de_CI_AI

-- Using back ticks is allowed, but unnecessary for builtin collations
`UTF8_BINARY`

Intercalación predeterminada

La intercalación predeterminada se aplica cuando se usan literales STRING, marcadores de parámetros, funciones sin parámetros STRING que producen cadenas y al definir tipos de columna, campo o variable sin una cláusula COLLATE.

La intercalación predeterminada se deriva de UTF8_BINARY.

Prioridad de intercalación

A fin de decidir qué intercalación se va a usar para una cadena determinada, Azure Databricks define reglas de precedencia de intercalación.

Las reglas asignan 4 niveles de prioridad a las intercalaciones:

1. Explícito

   La intercalación se ha asignado explícitamente a una cadena usando la expresión COLLATE.

   Ejemplos

   -- Force fast binary collation to check whether a vin matches a Ferrari
   vin COLLATE UTF8_BINARY LIKE 'ZFF%'
   
   -- Force German collation to order German first names
   ORDER BY vorname COLLATE DE
   

2. Implícito

   La intercalación se asigna implícitamente mediante las referencias a column, field, column-alias, variable o routine parameter. Esto incluye el resultado de una subconsulta siempre que la intercalación no sea None.

   Ejemplos

   -- Use the collation of the column as it was defined
   employee.name LIKE 'Mc%'
   
   -- Use the collation of the variable as it was defined.
   translate(session.tempvar, 'Z', ',')
   

3. Predeterminado

   Un literal STRING, marcador de parámetros con o sin nombre, o una STRING generada por una función de otro tipo.

   Ejemplos

   -- A literal string has the default collation
   'Hello'
   
   -- :parm1 is a parameter marker using session default collation
   EXECUTE IMMEDIATE 'SELECT :parm1' USING 'Hello' AS parm1;
   
   -- ? is a parameter marker using session default collation
   EXECUTE IMMEDIATE 'SELECT ?' USING 'Hello';
   
   -- The result of a cast of a non-STRING to a STRING is a STRING with the default collation
   CAST(5 AS STRING)
   
   -- The date is converted to a string using the default collation
   to_char(DATE'2016-04-08', 'y')
   
   -- The collation of the session_user STRING is the default collation
   session_user()
   

   La intercalación asignada es la intercalación predeterminada.

4. None

   Un resultado STRING de una función, un operador o una operación de conjunto (por ejemplo, UNION) que toma más de un argumento STRING y que tienen diferentes intercalaciones implícitas.

   Ejemplos

   -- Concatenating two strings with different explicit collations results in no collation
   SELECT fr || de AS freutsch FROM VALUES('Voulez vous ' COLLATE FR), 'Kartoffelsupp...' COLLATE DE) AS T(fr, de)
   
   -- A union of two strings with different excplicit collations results in no collation
   SELECT 'Voulez vous ' COLLATE FR UNION ALL SELECT 'Kartoffelsupp...' COLLATE DE
   

Derivación de intercalación

Al derivar la intercalación de un resultado de STRING, las reglas de precedencia de intercalación se aplican de las maneras siguientes:

Si la expresión:

1. coincide con las definiciones anteriores

   La intercalación y la precedencia quedan definidas.

2. es una función o un operador con un único parámetro de STRING y devuelve un elemento STRING

   La intercalación y la precedencia son las del parámetro STRING.

3. es una función o un operador con dos o más parámetros STRING

   1. con las mismas intercalaciones y prioridad

      La intercalación y la precedencia son las de los parámetros STRING.

   2. con diferentes intercalaciones o prioridad

      Deja que C1 y C2 sean intercalaciones distintas y deja que D sea la intercalación predeterminada. La precedencia y la intercalación se determinan mediante la tabla siguiente:

      Intercalación y prioridad	C1 Explícito	C1 Implícito	D Valor predeterminado	Ninguno
      C2 Explícito	Error	C2 Explícito	C2 Explícito	C2 Explícito
      C2 Implícito	C1 Explícito	Ninguno	C2 Implícito	Ninguno
      D Predeterminado	C1 Explícito	C1 Implícito	D Valor predeterminado	Ninguno
      Ninguno	C1 Explícito	Ninguno	Ninguno	Ninguno

Ejemplos

> SELECT 'hello' = 'hello   ' COLLATE UNICODE_RTRIM;
  true

> CREATE TABLE words(fr STRING COLLATE FR, de STRING COLLATE DE, en STRING COLLATE EN);
> INSERT INTO words VALUES ('Salut', 'Hallo', 'Hello');

-- A literal string has the default collation
> SELECT collation('Ciao');
  UTF8_BINARY

-- A function producing a STRING has the default collation
> SELECT collation(user());
  UTF8_BINARY

-- Function modifying a STRING passes the collation through
> SELECT collation(upper('Ciao'));
  UTF8_BINARY

-- Implicit collation (French) wins over default collation
> SELECT collation(fr || 'Ciao') FROM words;
  FR

-- Explicit collation (French) wins over implicit collation (German)
> SELECT collation('Salut' COLLATE FR || de) FROM words;
  FR

-- Implicit collation German collides with implicit collation French
-- The result is no collation
> SELECT collation(de || fr) FROM words;
  null

-- Explicit collation (French) wins over default collation (Italian)
> SELECT collation('Salut' COLLATE FR || 'Ciao');
  FR

-- Explicit collation (French) collides with explicit collation (German)
> SELECT collation('Salut' COLLATE FR || 'Hallo' COLLATE DE);
  COLLATION_MISMATCH.EXPLICIT

-- The explicit collation wins over no collation
> SELECT collation('Ciao' COLLATE IT || (fr || de)) FROM words;
  IT

-- The implict collation (English) does not win over None
> SELECT collation(en || (fr || de)) FROM words;
  null

-- The explicit collation (English) wins over Implicit collation anywhere in the expression
> SELECT collation((fr || ltrim('H' COLLATE EN, fr)) || fr) FROM words;
  EN

Temas relacionados

• expresión de agrupación
• Función de intercalación
• Función de tabla de clasificaciones