Database Software

Oracle Version 11.2.0.2.0

I have set NLS_COMP = 'LINGUISTIC' and NLS_SORT = 'BINARY_CI'.

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



This problem was only experienced in oracle version mentioned above, but in oracle 10.2.0.4.0 LIKE operator work perfectly.