oracle的collation

一、

In Microsoft SQL Server, if I want to search case insensitively in a case-sensitive database, I can run the following SQL:

SELECT*FROM MyTable
WHERE MyField ='BobDillon'COLLATE Latin1_General_CI_AI

And that will find all "bobdillon" entries.

If I want to do the same in Oracle, I know I can do this:

SELECT*FROM MyTable
WHERE UPPER(MyField)='BOBDILLON'

But I want to know if there is a direct equivalent to the collate keyword, so I can search for case sensitivity and accent sensitivity as I see fit.

 

 

 

a：SELECT*

FROM MyTable
WHERE NLSSORT(MyField,'NLS_SORT = Latin_CI')= NLSSORT('BobDillon','NLS_SORT = Latin_CI')

二、

Where can I query the current case-sensitivity setting of an oracle database?

I've tried looking in v$database, nls_database_parameters, and looking through the system packages, but none of them seem to provide the information I need...

In Oracle 10gR2:

SELECT*FROM    NLS_SESSION_PARAMETERS
WHERE   parameter IN('NLS_COMP','NLS_SORT')

SQL>ALTER SESSION SET NLS_COMP ='LINGUISTIC'2/

Session altered
SQL>SELECT  COUNT(*) FROM    dual WHERE'a'='A'

  COUNT(*)
----------
1

SQL>ALTER SESSION SET NLS_COMP ='BINARY'

Session altered
SQL>SELECT  COUNT(*) FROM    dual  WHERE'a'='A'

  COUNT(*)
----------
0

From documentation:

NLS_COMP specifies the collation behavior of the database session.

Values:

• BINARY

  Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless you specify the NLSSORT function.

• LINGUISTIC

  Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks should use the linguistic sort specified in the NLS_SORT parameter. To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.

• ANSI

  A setting of ANSI is for backwards compatibility; in general, you should set NLS_COMP to LINGUISTIC

 http://stackoverflow.com/questions/1244804/where-can-i-query-an-oracle-databases-case-sensitivity