2 Replies Latest reply: Feb 23, 2012 3:38 PM by 714266 RSS

    oracle case insensitive search issue

    714266
      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 the start of pattern)

      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

      thanks.

      Edited by: user2646520 on Feb 23, 2012 1:28 PM