[FIXED] how search accented letter in a varchar2 column in oracle

Issue

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!

Solution

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	اصفی

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

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	اصفی

Answered By - astentx
Answer Checked By - David Goodson (JavaFixing Volunteer)