Collate: Comparação e Ordenação de Caracteres

O que é Collate?

Muitos desenvolvedores confundem Charset com Collate. De forma simples:

• Charset: Define quais caracteres você pode salvar no banco de dados (ISO8859_1, WIN1252, UTF8, etc.)
• Collate: Define como esses caracteres são comparados e ordenados (ex: se 'A' é igual a 'a', se 'José' é igual a 'Jose', etc.)

O Collate vive na camada de comparação. Se você precisa que uma busca por "JOAO" retorne "João", você precisa de um collation Case Insensitive (CI) e Accent Insensitive (AI).

Por que Collate é importante?

Imagine que você tem uma tabela de clientes e precisa fazer uma busca. Sem o collate adequado, você teria que fazer conversões custosas via software (como UPPER() ou LOWER() em todas as queries), o que impede que o banco utilize os índices de forma eficiente.

O collate define regras como:

• Será que 'A' vem antes de 'á' na ordenação?
• Será que 'Pharmacia' e 'Farmacia' são a mesma coisa?
• Será que 'José' e 'Jose' também são iguais?
• Será que 'A' é igual a 'a' em comparações?

Quem cria os collates define isso. No Brasil não temos essa coisa de 'Ph' de pharmacia dos anos 30, mas em outros países podem haver agrupamentos de caracteres (collate) que devam ser tratados conjuntamente e não de forma individual.

Tipos de Collate: Case e Accent Sensitivity

Os collates geralmente são classificados por duas características principais:

• Case Sensitivity (CS/CI): Se diferencia maiúsculas de minúsculas
  • CI (Case Insensitive): 'A' = 'a' (não diferencia maiúsculas/minúsculas)
  • CS (Case Sensitive): 'A' ≠ 'a' (diferencia maiúsculas/minúsculas)
• Accent Sensitivity (AS/AI): Se diferencia caracteres acentuados
  • AI (Accent Insensitive): 'José' = 'Jose' (não diferencia acentos)
  • AS (Accent Sensitive): 'José' ≠ 'Jose' (diferencia acentos)

Exemplo prático: Criando tabelas com diferentes collates

IBExpert – Criar banco de dados:

C:\TEMP\TEST_COLLATE.FDB
CHARSET: UTF8
COLLATE: UNICODE_CI_AI
ALIAS: TEST_COLLATE.FDB
PORTA: 3040

Criar tabela com diferentes collates:

CREATE TABLE CLIENTES (
    ID INTEGER NOT NULL PRIMARY KEY,
    NOME_CS_AS VARCHAR(50) CHARACTER SET UTF8 COLLATE UNICODE,  -- Case e Accent Sensitive
    NOME_CI_AS VARCHAR(50) CHARACTER SET UTF8 COLLATE UNICODE_CI,  -- Case Insensitive, Accent Sensitive
    NOME_CI_AI VARCHAR(50) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,  -- Case e Accent Insensitive
    LOGIN VARCHAR(20) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI  -- Para buscas sem diferenciação
);

Equivalente em outros SGBDs:

• MS SQL Server:

  CREATE TABLE CLIENTES (
      ID INT PRIMARY KEY,
      NOME_CS_AS VARCHAR(50) COLLATE Latin1_General_CS_AS,  -- Case e Accent Sensitive
      NOME_CI_AS VARCHAR(50) COLLATE Latin1_General_CI_AS,  -- Case Insensitive, Accent Sensitive
      NOME_CI_AI VARCHAR(50) COLLATE SQL_Latin1_General_CP1252_CI_AI,  -- Case e Accent Insensitive
      LOGIN VARCHAR(20) COLLATE SQL_Latin1_General_CP1252_CI_AI
  );

• PostgreSQL:

  CREATE TABLE CLIENTES (
      ID SERIAL PRIMARY KEY,
      NOME_CS_AS VARCHAR(50) COLLATE "C",  -- Case e Accent Sensitive (binário)
      NOME_CI_AS VARCHAR(50) COLLATE "pt_BR.UTF-8",  -- Geralmente case insensitive, accent sensitive
      NOME_CI_AI VARCHAR(50),  -- Requer extensão específica ou função customizada
      LOGIN VARCHAR(20) COLLATE "pt_BR.UTF-8"
  );

Inserindo dados de teste:

INSERT INTO CLIENTES (ID, NOME_CS_AS, NOME_CI_AS, NOME_CI_AI, LOGIN)
VALUES (1, 'José', 'José', 'José', 'jose.silva');
INSERT INTO CLIENTES (ID, NOME_CS_AS, NOME_CI_AS, NOME_CI_AI, LOGIN)
VALUES (2, 'JOSE', 'JOSE', 'JOSE', 'JOSE.SILVA');
INSERT INTO CLIENTES (ID, NOME_CS_AS, NOME_CI_AS, NOME_CI_AI, LOGIN)
VALUES (3, 'Jose', 'Jose', 'Jose', 'jose');
INSERT INTO CLIENTES (ID, NOME_CS_AS, NOME_CI_AS, NOME_CI_AI, LOGIN)
VALUES (4, 'Maria', 'Maria', 'Maria', 'maria');
INSERT INTO CLIENTES (ID, NOME_CS_AS, NOME_CI_AS, NOME_CI_AI, LOGIN)
VALUES (5, 'MARIA', 'MARIA', 'MARIA', 'MARIA');
INSERT INTO CLIENTES (ID, NOME_CS_AS, NOME_CI_AS, NOME_CI_AI, LOGIN)
VALUES (6, 'Farmacia', 'Farmacia', 'Farmacia', 'farmacia');
INSERT INTO CLIENTES (ID, NOME_CS_AS, NOME_CI_AS, NOME_CI_AI, LOGIN)
VALUES (7, 'Farmácia', 'Farmácia', 'Farmácia', 'farmacia2');

COMMIT WORK;

Testando comparações com diferentes collates

Teste 1: Busca Case Sensitive vs Case Insensitive

Vamos testar se 'Jose' encontra 'JOSE' dependendo do collate:

-- Busca na coluna Case Sensitive (UNICODE)
SELECT * FROM CLIENTES 
WHERE NOME_CS_AS = 'Jose';
-- Resultado: Apenas o registro com 'Jose' (minúsculo)

-- Busca na coluna Case Insensitive (UNICODE_CI)
SELECT * FROM CLIENTES 
WHERE NOME_CI_AS = 'Jose';
-- Resultado: 'Jose', 'JOSE' e 'José' (encontra variações de maiúsculas/minúsculas)

-- Busca na coluna Case e Accent Insensitive (UNICODE_CI_AI)
SELECT * FROM CLIENTES 
WHERE NOME_CI_AI = 'Jose';
-- Resultado: 'Jose', 'JOSE', 'José' (encontra todas as variações)

Equivalente em outros SGBDs:

• MS SQL Server: A sintaxe é idêntica, apenas os nomes dos collates mudam.
• PostgreSQL: Use WHERE NOME_CI_AS ILIKE 'Jose' para case-insensitive, ou defina o collate na coluna.

Teste 2: Busca Accent Sensitive vs Accent Insensitive

Vamos testar se 'Farmacia' encontra 'Farmácia':

-- Busca na coluna Accent Sensitive
SELECT * FROM CLIENTES 
WHERE NOME_CS_AS = 'Farmacia';
-- Resultado: Apenas 'Farmacia' (sem acento)

-- Busca na coluna Accent Insensitive (UNICODE_CI_AI)
SELECT * FROM CLIENTES 
WHERE NOME_CI_AI = 'Farmacia';
-- Resultado: 'Farmacia' e 'Farmácia' (encontra ambas as variações)

Teste 3: Ordenação com diferentes collates

Vamos ver como a ordenação muda dependendo do collate:

-- Ordenação Case Sensitive (UNICODE)
SELECT NOME_CS_AS FROM CLIENTES 
ORDER BY NOME_CS_AS;
-- Resultado: Ordena primeiro maiúsculas, depois minúsculas
-- Exemplo: 'FARMACIA', 'JOSE', 'MARIA', 'Farmacia', 'Jose', 'Maria', 'Farmácia', 'José'

-- Ordenação Case Insensitive (UNICODE_CI)
SELECT NOME_CI_AS FROM CLIENTES 
ORDER BY NOME_CI_AS;
-- Resultado: Ignora maiúsculas/minúsculas na ordenação
-- Exemplo: 'Farmacia', 'Farmácia', 'Jose', 'JOSE', 'José', 'Maria', 'MARIA'

-- Ordenação Case e Accent Insensitive (UNICODE_CI_AI)
SELECT NOME_CI_AI FROM CLIENTES 
ORDER BY NOME_CI_AI;
-- Resultado: Ignora maiúsculas/minúsculas e acentos
-- Exemplo: 'Farmacia', 'Farmácia', 'Jose', 'JOSE', 'José', 'Maria', 'MARIA'

Exemplo prático: Collate em índices

O collate é especialmente importante quando você cria índices. Um índice criado com um collate específico só será usado eficientemente em queries que usam o mesmo collate:

-- Criar índice com collate Case e Accent Insensitive
CREATE INDEX IDX_CLIENTES_LOGIN ON CLIENTES (LOGIN);

-- Esta query usará o índice eficientemente
SELECT * FROM CLIENTES WHERE LOGIN = 'jose.silva';
-- Funciona porque LOGIN usa UNICODE_CI_AI

-- Mas se você fizer uma busca com collate diferente, o índice pode não ser usado
SELECT * FROM CLIENTES 
WHERE LOGIN COLLATE UNICODE = 'jose.silva';
-- Pode não usar o índice porque o collate é diferente

Collate e Charset: A relação

O collate está sempre atrelado a um charset. Sem o charset, o banco não teria a regionalidade de ordenação, ou quais sinais diacríticos são iguais a suas versões sem esses sinais.

Mesmo que um banco de dados tenha uma tabela usando 3 charsets diferentes, contendo os mesmos dados, o collate poderá fazer com que os dados se comportem usando a mesma regra linguística. Por exemplo, para o Brasil, case/accent insensitive significa que o collate não fará distinção entre maiúsculos e minúsculos e que a ordenação seguirá um mesmo padrão.

Exemplo: Mesmo charset, collates diferentes

CREATE TABLE TESTE_COLLATE (
    TEXTO_PT_BR VARCHAR(50) CHARACTER SET UTF8 COLLATE PT_BR,
    TEXTO_PT_PT VARCHAR(50) CHARACTER SET UTF8 COLLATE PT_PT,
    TEXTO_UNICODE VARCHAR(50) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI
);

INSERT INTO TESTE_COLLATE VALUES ('Farmacia', 'Farmacia', 'Farmacia');
INSERT INTO TESTE_COLLATE VALUES ('Farmácia', 'Farmácia', 'Farmácia');
INSERT INTO TESTE_COLLATE VALUES ('José', 'José', 'José');
INSERT INTO TESTE_COLLATE VALUES ('Jose', 'Jose', 'Jose');

-- Ordenação com PT_BR
SELECT TEXTO_PT_BR FROM TESTE_COLLATE ORDER BY TEXTO_PT_BR;
-- Ordenação com PT_PT
SELECT TEXTO_PT_PT FROM TESTE_COLLATE ORDER BY TEXTO_PT_PT;
-- Ordenação com UNICODE_CI_AI
SELECT TEXTO_UNICODE FROM TESTE_COLLATE ORDER BY TEXTO_UNICODE;

Você notará que a ordenação pode ser diferente entre PT_BR e PT_PT, mesmo usando o mesmo charset UTF8, porque cada collate segue regras linguísticas específicas de sua região.

Collates disponíveis em diferentes SGBDs

Recomendações práticas

Quando usar cada tipo de collate:

• Case e Accent Insensitive (CI_AI):
  • Campos de login, email, códigos
  • Quando você quer que 'JOAO' encontre 'João'
  • Campos de busca onde o usuário pode digitar sem acentos
• Case Insensitive, Accent Sensitive (CI_AS):
  • Nomes próprios onde acentos são importantes
  • Quando 'José' deve ser diferente de 'Jose', mas 'JOSE' = 'Jose'
• Case e Accent Sensitive (CS_AS):
  • Senhas, tokens, códigos exatos
  • Quando a diferenciação exata é crítica

Boas práticas:

• Escolha o collate adequado na criação da tabela: Evite ter que fazer conversões em todas as queries
• Use o mesmo collate no índice e na coluna: Para garantir uso eficiente dos índices
• Considere o collate do banco: Se não especificar, a coluna herdará o collate padrão do banco
• Para aplicações brasileiras: PT_BR ou UNICODE_CI_AI são geralmente boas escolhas
• Teste as comparações: Sempre teste se suas queries encontram os dados esperados com o collate escolhido

← Voltar para o SGBD Hub