This discussion is archived
4 Replies Latest reply: Mar 5, 2013 10:40 AM by 761885 RSS

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

796900 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points