Searching for Clients Does Not Return Expected Results If Client Name Has an Accented Character - Accent Sensitive Search (Doc ID 1645506.1)

Last updated on MAY 01, 2017

Applies to:

Oracle Insurance Policy Administration J2EE - Version 9.6.1.7 and later
Information in this document applies to any platform.

Goal

 Searching for fields with accented characters (ex. André) only returns exact character matches. 

 

The following testing outlines the testing completed to verify the solution offered:

SELECT * FROM nls_session_parameters WHERE PARAMETER IN ('NLS_SORT', 'NLS_COMP')

PARAMETER     VALUE    
------------  --------
NLS_SORT      BINARY  
NLS_COMP      BINARY  

2 record(s) selected

CREATE TABLE ACCCENTTEST ( ClientName VARCHAR2(150) NOT NULL,CONSTRAINT PK_ACCCENTTEST PRIMARY KEY(ClientName))

INSERT INTO ACCCENTTEST (ClientName)  VALUES('John')
INSERT INTO ACCCENTTEST (ClientName) VALUES('JOHN')
INSERT INTO ACCCENTTEST (ClientName) VALUES('Andre')
INSERT INTO ACCCENTTEST (ClientName)  VALUES('ANDRE')
INSERT INTO ACCCENTTEST (ClientName)  VALUES('André')

SELECT * FROM ACCCENTTEST
CLIENTNAME    
-------------
John          
JOHN          
Andre        
ANDRE        
André        

5 record(s) selected

SELECT * FROM ACCCENTTEST WHERE ClientName LIKE 'Andre%'
CLIENTNAME    
-------------
Andre        

1 record(s) selected

CREATE INDEX CI_1_ACCCENTTEST ON ACCCENTTEST(NLSSORT(CLIENTNAME,'NLS_SORT=BINARY_CI'))

SELECT * FROM ACCCENTTEST WHERE ClientName LIKE 'Andre%'
CLIENTNAME    
-------------
Andre        

1 record(s) selected

ALTER SESSION SET NLS_COMP = 'LINGUISTIC'
ALTER SESSION SET NLS_SORT = 'BINARY_CI'

SELECT * FROM nls_session_parameters WHERE PARAMETER IN ('NLS_SORT', 'NLS_COMP')

PARAMETER     VALUE      
------------  ----------
NLS_SORT      BINARY_CI  
NLS_COMP      LINGUISTIC

2 record(s) selected

SELECT * FROM ACCCENTTEST WHERE ClientName LIKE 'Andre%'
CLIENTNAME    
-------------
Andre        
ANDRE        

2 record(s) selected

DROP INDEX CI_1_ACCCENTTEST

CREATE INDEX AI_1_ACCCENTTEST ON
ACCCENTTEST(NLSSORT(CLIENTNAME,'NLS_SORT=BINARY_AI'))


ALTER SESSION SET NLS_COMP = 'LINGUISTIC'
ALTER SESSION SET NLS_SORT = 'BINARY_AI'

SELECT * FROM nls_session_parameters WHERE PARAMETER IN ('NLS_SORT','NLS_COMP')

PARAMETER     VALUE      
------------  ----------
NLS_SORT      BINARY_AI  
NLS_COMP      LINGUISTIC

2 record(s) selected

SELECT * FROM ACCCENTTEST WHERE ClientName LIKE 'Andre%'
CLIENTNAME    
-------------
Andre        
ANDRE        
André        

3 record(s) selected

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms