CODEWITHSUNDEEP
sqloracle-database
Tiasn
Tiasn

Reputation: 305

Compare Strings ignoring accents in SQL (ORACLE)

I would like to know if there is an easy way to compare two text values ignoring the accents and upper case. Im working with an Oracle database. I already searched for an answer but honestly I don't understand what they proposed in here Accent and case insensitive collation in Oracle with LIKE. I tried that and it didn't work for me. Basically all I want to do is two compare to text values like 'pepé' and 'pepe' and obtain true as answer.

is it possible to do it without the LIKE instruction?

Thank You!

Upvotes: 15

Views: 16664

Answers (6)

scuro
scuro

Reputation: 868

Since Oracle 12.2 there is the collate keyword which can be used with binary_ci for a case insensitive search and with binary_ai for both case and accent insensitive. This is also a more index-friendly method, but I am not aware of the exact details regarding indexes.

SELECT age FROM persons WHERE name = 'pepe' COLLATE binary_ai

Matches on: Pepé

Source: https://blogs.oracle.com/sql/post/how-to-do-case-insensitive-and-accent-insensitive-search-in-oracle-database

Upvotes: 2

Caio
Caio

Reputation: 41

As shown in the following blog: https://eduardolegatti.blogspot.com/2013/04/ignorando-caracteres-acentuados.html

The @AJPerez reply is valid but you might have problem by using it with LIKE operator. As @Just Cavin suggested, execute it before your query: (Versions after 10g R2)

ALTER SESSION SET NLS_SORT='WEST_EUROPEAN_AI'; -- Accent Insensitive ALTER SESSION SET NLS_COMP='LINGUISTIC'; -- Case Insensitive

Upvotes: 0

Titukedo
Titukedo

Reputation: 960

From this thread you can do the following:

select * from your_table 
where upper(utl_raw.cast_to_varchar2((nlssort(your_column, 'nls_sort=binary_ai')))) like upper('%word%')

Upvotes: 1

Kleyton R. Ribeiro
Kleyton R. Ribeiro

Reputation: 31

This works for Ç and the rest of characters in PT-BR (Portuguese - Brazil):

SELECT CONVERT( 'ÃÕÑ ÁÉÍ Ç', 'SF7ASCII' ) FROM DUAL;
Result: **AON AEI C**

Check your database version:

SELECT * FROM V$VERSION;
   Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    PL/SQL Release 11.2.0.1.0 - Production
    "CORE   11.2.0.1.0  Production"
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production

Upvotes: 2

collapsar
collapsar

Reputation: 17238

use the nlssort function in the following way:

select * from <your_table> where utl_raw.cast_to_varchar2((nlssort(<inspected_column>, 'nls_sort=binary_ai'))) like 'pe%';

The nlssort call transforms accented characters to their linguistic bases and ignores case in comparisons.

Original source is this article (verified on 12c).

Upvotes: 6

MT0
MT0

Reputation: 168416

Oracle Setup:

CREATE TABLE TABLE_NAME ( value ) AS
SELECT 'pepé' FROM DUAL;

-- Not necessary to create an index but it can speed things up.
CREATE INDEX value_without_accent_idx
  ON TABLE_NAME ( CONVERT( value, 'US7ASCII' ) );

Query:

SELECT *
FROM   table_name
WHERE  CONVERT( value, 'US7ASCII' ) = 'pepe';

Output:

VALUE
-----
pepé

Upvotes: 13

Related Questions