Home » SQL & PL/SQL » SQL & PL/SQL » Accent insensitive comparing problem (Oracle 8i)
Accent insensitive comparing problem [message #424219] Wed, 30 September 2009 16:34 Go to next message
Jaime Stuardo
Messages: 57
Registered: March 2004

I need to compare a string using accent insensitive comparing.

I tried to issuing this command:

but that NLS parameter is not recognized.

I tried other variations:

and neither worked.

Any help will be appreciated

Re: Accent insensitive comparing problem [message #424226 is a reply to message #424219] Wed, 30 September 2009 21:47 Go to previous messageGo to next message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I haven't played around with this stuff, but I think I recall reading that NLS_SORT will only affect sort order - i.e. ORDER BY clauses.

The comparison operators (=, <>, >[=], <[=], LIKE, BETWEEN) all operate on the internal RAW representation of the column value.

The options that I can think of are:
  • Use a TRANSLATE() function to convert accented characters into un-accented characters. You could even create a function-based index on the expression for performance.
  • There is a capability where you can create your own operators using PL/SQL functions. You could create an accent-insensitive comparison operator. I'm really out of my depth on this stuff, but Barbara Boehmer - an OraFAQ regular - is a dead-set expert and may be reading this thread.

I would be surprised if this question has not been asked and answered previously. Have you searched?

Ross Leishman
Re: Accent insensitive comparing problem [message #424309 is a reply to message #424226] Thu, 01 October 2009 09:00 Go to previous messageGo to next message
Jaime Stuardo
Messages: 57
Registered: March 2004
And what about NLS_COMP?

According to documentation it's possible to do it by using NLS_COMP and NLS_SORT settings but both didn't help in my case.

I tried for example

but "=" operator retrieves all the records that has or has not the condition equal true.

Neither solution in this forum has worked.


[Updated on: Thu, 01 October 2009 09:00]

Report message to a moderator

Re: Accent insensitive comparing problem [message #424327 is a reply to message #424219] Thu, 01 October 2009 10:09 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
This guy says something about it on page 2.

Two articles ago in The Globalization of Language in Oracle - The NLS_COMP and NLS_SORT variables, we touched on the ability to have case insensitivity and accent insensitivity for a character. In this article we set NLS_COMP=ANSI and NLS_SORT=BINARY_CI (case insensitivity) or NLS_SORT=BINARY_AI (accent and case insensitivity). The difference being that under NLS_SORT=BINARY_CI (case insensitivity) a=A and ä=Ä. BUT under NLS_SORT=BINARY_AI (accent and case insensitivity) a=A=ä=Ä.

In pre-Oracle10g versions of Oracle, in order to get this type of equality of characters you needed to use a combination of the NLS_UPPER and NLS_LOWER functions or use the NLS_SORT setting of GENERIC_BASELETTER. While these approaches worked they do have the flaw of having to programmatically code the NLS_UPPER or NLS_LOWER functions into every select statement. Or when using the GENERIC_BASELETTER you had a solution that was not a true linguistic solution for character comparison.

Also there is this from one of our own.
Hope it helps but you may already know it.


[Updated on: Thu, 01 October 2009 10:15]

Report message to a moderator

Previous Topic: procedure fails due to role
Next Topic: How to Calculate the number of periods for the given Start date and End date
Goto Forum:

Current Time: Thu Aug 17 16:37:03 CDT 2017

Total time taken to generate the page: 0.05315 seconds