how search accented letter in a varchar2 column in oracle-CodePudding

I have a varchar2 column named NAME_USER. For example, the data is آصفی. I have a query that searches the table to find rows that like some given character. When I want to search for example اصفی it doesn't fetch anything! But I want my query to fetches all different types of ( اَ اِ اٌ آ اُ ا ٲ ٳ ).

Is there any way to have a query that searches all types of a character not only the exact given one!

CodePudding user response：

I don't recognize script you're using (Google Translator "discovered" it is Persian?) so here's example using Latin. I'm searching for strings that contain vowels i or u, regardless letter case (upper or lower; that's the last regexp_like parameter, 'i'):

SQL> with test (col) as
  2    (select 'Littlefoot'  from dual union all
  3     select 'King Arthur' from dual union all
  4     select 'Lord Nelson' from dual
  5    )
  6  select col
  7  from test
  8  where regexp_like(col, 'i|u', 'i');

COL
-----------
Littlefoot
King Arthur

SQL>

As the last string, Lord Nelson, doesn't contain i nor u, it has not been returned.

Therefore, I presume that something like that should work for you as well, i.e. separate all sorts of that character with a pipe | sign and give it a try.

CodePudding user response：

Unfortunately, I totally don't know the alphabet and language you use to be able to generate additional test cases. But what you are looking for is an accent-insensitive comparison, which is designated by _AI suffix for NLS_SORT parameter. For more information see Globalization support guide.

Assuming this sample data:

select *
from t
ID UNAME

1 آصفی

2 اصفی

ID	UNAME
1	آصفی
2	اصفی

You may use NLSSORT function to tweak a single comparison:

select
  t.*,
  nlssort(uname, 'NLS_SORT=BINARY_AI') as nlskey
from t
where nlssort(uname, 'NLS_SORT=BINARY_AI')
  = nlssort('اصفی', 'NLS_SORT=BINARY_AI')
ID UNAME NLSKEY

1 آصفی 0xD8A7D8B5D981DB8C00

2 اصفی 0xD8A7D8B5D981DB8C00

ID	UNAME	NLSKEY
1	آصفی	0xD8A7D8B5D981DB8C00
2	اصفی	0xD8A7D8B5D981DB8C00

Or set such behaviour for the entire session and all comparisons:

alter session set nls_sort=BINARY_AI

alter session set nls_comp=linguistic

select *
from t
where uname = 'اصفی'
ID UNAME

1 آصفی

2 اصفی