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 -
Links -
https://community.oracle.com/thread/2400229
https://stackoverflow.com/questions/6682173/oracle-search-text-with-non-english-characters
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