Tuesday, March 20, 2018

Accent Insensitive search in Oracle

Here is way to search non english characters in Oracle :  Please see the example demo below -

Here I have created a table "accent" with few accent and non-accent data. then tried to search - 

 CREATE TABLE accent (name VARCHAR2 (200));  
 
  INSERT INTO accent  
    VALUES ('Peña Báináõ');  
 
  INSERT INTO accent  
    VALUES ('Pena Bainao');  
 
  INSERT INTO accent  
    VALUES ('Pena Bainaoooo');  
 
  INSERT INTO accent  
    VALUES ('Penaffff Bainao');  
 
  COMMIT;  
 
 
  SELECT UPPER (  
      UTL_RAW.cast_to_varchar2 (NLSSORT (name, 'nls_sort=binary_ai')))  
      name  
  FROM accent;  
 
 
 
  SELECT *  
  FROM (SELECT UPPER (  
          UTL_RAW.cast_to_varchar2 (  
            NLSSORT (name, 'nls_sort=binary_ai')))  
          name  
      FROM accent)  
  WHERE UPPER (TRIM (name)) LIKE UPPER ('PENA BAINAO%');  
 
 
   --example from dual table  
 
  SELECT 'Peña Báináõ' original,  
     UTL_RAW.cast_to_varchar2 (  
      NLSSORT ('Peña Báináõ', 'nls_sort=binary_ai'))  
      converted_like_english  
  FROM DUAL;  


Links -
https://community.oracle.com/thread/2400229
https://stackoverflow.com/questions/6682173/oracle-search-text-with-non-english-characters