This content has been marked as final. Show 2 replies
user2646520 wrote:We would need to know the datatype of ACC_NO, and what constitutes 'wrong results'. What data are you not getting that you think you should? What data are you getting that you think you shouldn't?
I need to case insensitive search for my oracle server.
I have set NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_CI
it works well but when I execute a query like this:
SELECT MAX(SUBSTRING(acc_no, 9, 3)) FROM T_Account WHERE (acc_no LIKE '100011130%');
(without % on start)
it gives wrong results.
I have googled it, this looks like a nasty bug. Problem with LIKE when using NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_AI
how could I fix this with minimum changes of my queries?
I am using oracle 11gr2
the type of ACC_NO is NVARCHAR2(12)
it returns some wrong rows
for example rows with ACC_NO =236290010008 or ACC_NO=236291030013