Ignorando caracteres acentuados, maiúsculos e minúsculos no Oracle (Case Insensitive/Accent Insensitive)

Olá,

Dê uma boa olhada no resultado do SQL abaixo. Em que ordem os caracteres vieram ordenados?

SQL> select nome from t1 order by 1;

NOME
----
A
a
á
Z
z
1
10
2
9

9 linhas selecionadas.

De acordo com o CHARACTERSET em uso no banco de dados, seja ele WE8ISO8859P1 ou WE8MSWIN1252 (ambos muito utilizados aqui no Brasil), quando ordenamos caracteres alfanuméricos fazendo uso da claúsula ORDER BY em um SELECT, vemos caracteres alfabéticos antes dos caracteres numéricos. No cenário do meu banco de dados, isso ocorre porque o parâmetro NLS_SORT está definido com o valor WEST_EUROPEAN.

SQL> select * from nls_session_parameters;

PARAMETER                      VALUE
------------------------------ -------------------------------
NLS_LANGUAGE                   BRAZILIAN PORTUGUESE
NLS_TERRITORY                  BRAZIL
NLS_CURRENCY                   R$
NLS_ISO_CURRENCY               BRAZIL
NLS_NUMERIC_CHARACTERS         ,.
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD/MM/YYYY HH24:MI:SS
NLS_DATE_LANGUAGE              BRAZILIAN PORTUGUESE
NLS_SORT                       WEST_EUROPEAN
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              Cr$
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 linhas selecionadas.

O mesmo efeito será obtido alterando a sessão no banco de dados com o comando "ALTER SESSION" abaixo:

SQL> ALTER SESSION SET NLS_SORT='WEST_EUROPEAN';

Sessão alterada.

SQL> select nome from t1 order by 1;

NOME
----
A
a
á
Z
z
1
10
2
9

9 linhas selecionadas.

Se quisermos que os caracteres numéricos apareçam antes dos caracteres alfabéticos, basta alteramos a sessão para que o parâmetro NLS_SORT seja definido como BINARY. O valor BINARY significa que a ordenação dos caracteres se seguirão de acordo com a ordem em que os caracteres aparecem no Character Set em uso. Fazendo uma analogia, seria como seguir a ordem da numeração dos caracteres na tabela ASCII. Veja o exemplo abaixo:

SQL> ALTER SESSION SET NLS_SORT='BINARY';

Sessão alterada.

SQL> select nome from t1 order by 1;

NOME
----
1
10
2
9
A
Z
a
z
á

9 linhas selecionadas.

Podemos verificar acima que, além do caracteres numéricos virem antes dos caracteres alfabéticos, vemos que o caractere "a" minúsculo veio após o caractere "Z" maiúsculo. Para que a ordenação seja Case Insensitive, basta alterarmos NLS_SORT para BINARY_CI conforme demonstração abaixo:

SQL> ALTER SESSION SET NLS_SORT='BINARY_CI';

Sessão alterada.

SQL> select nome from t1 order by 1;

NOME
----
1
10
2
9
a
A
Z
z
á

9 linhas selecionadas.

Já em relação a acentuação, podemos verificar que o caractere "á" ("a" minúsculo acentuado) veio após o caractere "z" minúsculo. Para que a ordenação seja não só Case Insensitive, mas também Accent Insensitive, basta alterarmos NLS_SORT para BINARY_AI, conforme demonstração abaixo:

SQL> ALTER SESSION SET NLS_SORT='BINARY_AI';

Sessão alterada.

SQL> select nome from t1 order by 1;

NOME
----
1
10
2
9
a
á
A
Z
z

9 linhas selecionadas.

Uma sintaxe que podemos utilizar de forma que não tenhamos que ficar alterando a sessão no banco de dados a todo momento, é utilizar a função NLSSORT, conforme exemplo a seguir:

SQL> select * from t1 order by NLSSORT(nome,'NLS_SORT=BINARY_CI');

NOME
----
1
10
2
9
a
A
Z
z
á

9 linhas selecionadas.

Bom, a figura abaixo nos mostra um resumo da ordem em que aparecerão os caracteres alfanuméricos dependendo do valor que estiver setado em NLS_SORT.

Já em relação à ignorar caracteres acentuados, maiúsculos e minúsculos em uma pesquisa, tenha como base os dados da tabela abaixo:

SQL> select * from t2;

NOME
--------------------
Joao
joao
JOAO
joão
JOÃO
JoÃo

6 linhas selecionadas.

Uma maneira prática e manual de pesquisarmos todos os "João(s)" acima ignorando os caracteres acentuados, maiúsculos e minúsculos, seria utilizando a função TRANSLATE conforme exemplo a seguir:

SQL> select nome
  2  from t2
  3  where
  4  upper
  5  (translate(nome,
  6  'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü',
  7  'ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu'))
  8  LIKE
  9  upper((translate('%joao%',
 10  'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü',
 11  'ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu')));

NOME
--------------------
Joao
joao
JOAO
joão
JOÃO
JoÃo

6 linhas selecionadas.

Bom, além de utilizar a solução acima, teria alguma outra de forma a se obter o mesmo resultado? Sim. Além de setarmos NLS_SORT, basta setarmos também o parâmetro NLS_COMP para ANSI (até o 10g R1) ou LINGUISTIC (à partir do 10g R2). Por padrão o seu valor é BINARY. Como exemplo, realizando o "alter session" abaixo, iremos fazer com que o Oracle ordene e compare linguisticamente ignorando caracteres maiúsculos e minúsculos. (CI = somente Case Insensitive).

SQL> ALTER SESSION SET NLS_SORT='WEST_EUROPEAN_CI';

Sessão alterada.

SQL> ALTER SESSION SET NLS_COMP='ANSI';

Sessão alterada.

SQL> select * from t2 where nome = 'joao';

NOME
--------------------
Joao
joao
JOAO

Para ignorar caracteres acentuados, poderemos setar NLS_SORT para WEST_EUROPEAN_AI (AI = Accent Insensitive e Case Insensitive):

SQL> ALTER SESSION SET NLS_SORT='WEST_EUROPEAN_AI';

Sessão alterada.

SQL> select * from t2 where nome = 'joao';

NOME
--------------------
Joao
joao
JOAO
joão
JOÃO
JoÃo

6 linhas selecionadas.

Uma limitação do NLS_COMP=ANSI se mostra evidente quando fazemos uso do operador LIKE, conforme demonstração a seguir:

SQL> select * from t2 where nome LIKE ('%joao%');

NOME
--------------------
joao

Como podemos ver no resultado acima, apenas um registro foi retornado, ou seja, a pesquisa não ignorou caracteres acentuados, maiúsculos e minúsculos. É por esse motivo que à partir do Oracle 10g R2, o valor "ANSI" está obsoleto conforme a documentação: "A setting of ANSI is for backwards compatibility; in general, you should set NLS_COMP to LINGUISTIC". Portanto, alterando o valor de NLS_COMP para LINGUISTIC, poderemos verificar abaixo que a pesquisa utilizando o operador LIKE irá ignorar os caracteres acentuados, maiúsculos e minúsculos.

SQL> ALTER SESSION SET NLS_SORT='WEST_EUROPEAN_AI';

Sessão alterada.

SQL> ALTER SESSION SET NLS_COMP='LINGUISTIC';

Sessão alterada.

SQL> select * from t2 where nome LIKE ('%joao%');

NOME
--------------------
Joao
joao
JOAO
joão
JOÃO
JoÃo

6 linhas selecionadas.