4 Replies Latest reply: Mar 5, 2013 12:40 PM by user970493 RSS

    Problem with LIKE when using NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_AI

    796900
      Oracle Version: 11.2.0.1.0

      My goal is to try and make all connections case insensitive. In my SPFILE, I have set NLS_COMP = 'LINGUISTIC' and NLS_SORT = 'BINARY_AI'.

      Whenever I use a LIKE comparison without a leading wildcard (%), it does not return the correct results.
      Here is a generic example. My problem is obviously with Query #3. Anyone experienced this before?

      CREATE TABLE People (
        ID NUMBER(1,0),
        FirstName NVARCHAR2(20),
        LastName NVARCHAR2(20));
      INSERT INTO People (ID, FirstName, LastName) VALUES ('1', 'John', 'Doe');
      INSERT INTO People (ID, FirstName, LastName) VALUES ('2', 'Jane', 'Doe');
      INSERT INTO People (ID, FirstName, LastName) VALUES ('3', 'Rich', 'Donner');
      INSERT INTO People (ID, FirstName, LastName) VALUES ('4', 'Mike', 'Redoer');
      -- Query #1
      SELECT ID FROM People WHERE Lastname = 'doe';
      
      -- Results (Correct)
      -- 1
      -- 2
      -- Query #2
      SELECT ID FROM People WHERE Lastname LIKE '%doe%';
      
      -- Results (Correct)
      -- 1
      -- 2
      -- 4
      -- Query #3
      SELECT ID FROM People WHERE Lastname LIKE 'do%';
      
      -- Results (Incorrect)
      -- 1
      -- 2
      -- 3
      -- 4