Oracle® Database Globalization Support Guide 10g Release 2 (10.2) Part Number B14225-02 |
|
|
View PDF |
This chapter explains sorting and searching for strings in an Oracle environment.
This chapter contains the following topics:
Different languages have different sort orders. In addition, different cultures or countries that use the same alphabets may sort words differently. For example, in Danish, Æ is after Z
, while Y
and Ü
are considered to be variants of the same letter.
Sort order can be case-sensitive or case-insensitive. Case refers to the condition of being uppercase or lowercase. For example, in a Latin alphabet, A
is the uppercase glyph for a
, the lowercase glyph.
Sort order can ignore or consider diacritics. A diacritic is a mark near or through a character or combination of characters that indicates a different sound than the sound of the character without the diacritic. For example, the cedilla (,
) in façade
is a diacritic. It changes the sound of c
.
Sort order can be phonetic or it can be based on the appearance of the character. For example, sort order can be based on the number of strokes in East Asian ideographs. Another common sorting issue is combining letters into a single character. For example, in traditional Spanish, ch
is a distinct character that comes after c
, which means that the correct order is: cerveza, colorado, cheremoya. This means that the letter c
cannot be sorted until Oracle has checked whether the next letter is an h
.
Oracle provides the following types of sorts:
Binary sort
Monolingual linguistic sort
Multilingual linguistic sort
These sorts achieve a linguistically correct order for a single language as well as a sort based on the multilingual ISO standard (ISO 14651), which is designed to handle many languages at the same time.
One way to sort character data is based on the numeric values of the characters defined by the character encoding scheme. This is called a binary sort. Binary sorts are the fastest type of sort. They produce reasonable results for the English alphabet because the ASCII and EBCDIC standards define the letters A to Z in ascending numeric value.
Note: In the ASCII standard, all uppercase letters appear before any lowercase letters. In the EBCDIC standard, the opposite is true: all lowercase letters appear before any uppercase letters. |
When characters used in other languages are present, a binary sort usually does not produce reasonable results. For example, an ascending ORDER
BY
query returns the character strings ABC
, ABZ
, BCD
, ÄBC
, when Ä
has a higher numeric value than B
in the character encoding scheme. A binary sort is not usually linguistically meaningful for Asian languages that use ideographic characters.
To produce a sort sequence that matches the alphabetic sequence of characters, another sort technique must be used that sorts characters independently of their numeric values in the character encoding scheme. This technique is called a linguistic sort. A linguistic sort operates by replacing characters with numeric values that reflect each character's proper linguistic order.
Oracle offers two kinds of linguistic sorts: monolingual and multilingual.
This section includes the following topics:
Oracle compares character strings in two steps for monolingual sorts. The first step compares the major value of the entire string from a table of major values. Usually, letters with the same appearance have the same major value. The second step compares the minor value from a table of minor values. The major and minor values are defined by Oracle. Oracle defines letters with diacritic and case differences as having the same major value but different minor values.
Each major table entry contains the Unicode code point and major value for a character. The Unicode code point is a 16-bit binary value that represents a character.
Table 5-1 illustrates sample values for sorting a
, A
, ä
, Ä
, and b
.
Table 5-1 Sample Glyphs and Their Major and Minor Sort Values
Glyph | Major Value | Minor Value |
---|---|---|
a |
15 |
5 |
A |
15 |
10 |
ä |
15 |
15 |
Ä |
15 |
20 |
b |
20 |
5 |
Note: Monolingual linguistic sorting is not available for non-Unicode multibyte database character sets. If a monolingual linguistic sort is specified when the database character set is non-Unicode multibyte, then the default sort order is the binary sort order of the database character set. One exception isUNICODE_BINARY . This sort is available for all character sets. |
Oracle provides multilingual linguistic sorts so that you can sort data in more than one language in one sort. This is useful for regions or languages that have complex sorting rules and for multilingual databases. Oracle Database 10g supports all of the sort orders defined by previous releases.
For Asian language data or multilingual data, Oracle provides a sorting mechanism based on the ISO 14651 standard and the Unicode 4.0 standard. Chinese characters are ordered by the number of strokes, PinYin, or radicals.
In addition, multilingual sorts can handle canonical equivalence and supplementary characters. Canonical equivalence is a basic equivalence between characters or sequences of characters. For example, ç
is equivalent to the combination of c
and ,
. Supplementary characters are user-defined characters or predefined characters in Unicode 4.0 that require two code points within a specific code range. You can define up to 1.1 million code points in one multilingual sort.
For example, Oracle supports a monolingual French sort (FRENCH
), but you can specify a multilingual French sort (FRENCH_M
). _M
represents the ISO 14651 standard for multilingual sorting. The sorting order is based on the GENERIC_M
sorting order and can sort diacritical marks from right to left. Oracle Corporation recommends using a multilingual linguistic sort if the tables contain multilingual data. If the tables contain only French, then a monolingual French sort may have better performance because it uses less memory. It uses less memory because fewer characters are defined in a monolingual French sort than in a multilingual French sort. There is a tradeoff between the scope and the performance of a sort.
Oracle evaluates multilingual sorts at three levels of precision:
A primary level sort distinguishes between base letters, such as the difference between characters a
and b
. It is up to individual locales to define whether a
is before b
, b
is before a
, or if they are equal. The binary representation of the characters is completely irrelevant. If a character is an ignorable character, then it is assigned a primary level order (or weight) of zero, which means it is ignored at the primary level. Characters that are ignorable on other levels are given an order of zero at those levels.
For example, at the primary level, all variations of bat
come before all variations of bet
. The variations of bat
can appear in any order, and the variations of bet
can appear in any order:
Bat bat BAT BET Bet bet
A secondary level sort distinguishes between base letters (the primary level sort) before distinguishing between diacritics on a given base letter. For example, the character Ä
differs from the character A
only because it has a diacritic. Thus, Ä
and A
are the same on the primary level because they have the same base letter (A
) but differ on the secondary level.
The following list has been sorted on the primary level (resume
comes before resumes
) and on the secondary level (strings without diacritics come before strings with diacritics):
resume résumé Résumé Resumes resumes résumés
A tertiary level sort distinguishes between base letters (primary level sort), diacritics (secondary level sort), and case (upper case and lower case). It can also include special characters such as +
, -
, and *
.
The following are examples of tertiary level sorts:
Characters a
and A
are equal on the primary and secondary levels but different on the tertiary level because they have different cases.
Characters ä
and A
are equal on the primary level and different on the secondary and tertiary levels.
The primary and secondary level orders for the dash character -
is 0. That is, it is ignored on the primary and secondary levels. If a dash is compared with another character whose primary level order is nonzero, for example, u
, then no result for the primary level is available because u
is not compared with anything. In this case, Oracle finds a difference between -
and u
only at the tertiary level.
The following list has been sorted on the primary level (resume
comes before resumes
) and on the secondary level (strings without diacritics come before strings with diacritics) and on the tertiary level (lower case comes before upper case):
resume Resume résumé Résumé resumes Resumes résumés Résumés
This section contains information about different features that a linguistic sort can have:
You can customize linguistic sorts to include the desired characteristics.
Base letters are defined in a base letter table, which maps each letter to its base letter. For example, a
, A
, ä
, and Ä
all map to a
, which is the base letter. This concept is particularly relevant for working with Oracle Text.
Some characters can be ignored in a linguistic sort. These characters are called ignorable characters. There are two kinds of ignorable characters: diacritics and punctuation.
Examples of ignorable diacritics are:
^
, so that rôle
is treated the same as role
The umlaut, so that naïve
is treated the same as naive
An example of an ignorable punctuation character is the dash character -
. If it is ignored, then multi-lingual
can be treated that same as multilingual
and e-mail
can be treated the same as email
.
Sorting elements usually consist of a single character, but in some locales, two or more characters in a character string must be considered as a single sorting element during sorting. For example, in traditional Spanish, the string ch
is composed of two characters. These characters are called contracting characters in multilingual linguistic sorting and special combination letters in monolingual linguistic sorting.
Do not confuse a composed character with a contracting character. A composed character like á
can be decomposed into a
and '
, each with their own encoding. The difference between a composed character and a contracting character is that a composed character can be displayed as a single character on a terminal, while a contracting character is used only for sorting, and its component characters must be rendered separately.
In some locales, certain characters must be sorted as if they were character strings. An example is the German character ß
(sharp s). It is sorted exactly the same as the string SS
. Another example is that ö
sorts as if it were oe
, after od
and before of
. These characters are known as expanding characters in multilingual linguistic sorting and special letters in monolingual linguistic sorting. Just as with contracting characters, the replacement string for an expanding character is meaningful only for sorting.
In Japanese, a prolonged sound mark that resembles an em dash —
represents a length mark that lengthens the vowel of the preceding character. The sort order depends on the vowel that precedes the length mark. This is called context-sensitive sorting. For example, after the character ka
, the —
length mark indicates a long a
and is treated the same as a
, while after the character ki
, the —
length mark indicates a long i
and is treated the same as i
. Transliterating this to Latin characters, a sort might look like this:
kaa ka— -- kaa and ka— are the same kai -- kai follows ka- because i is after a kia -- kia follows kai because i is after a kii -- kii follows kia because i is after a ki— -- kii and ki— are the same
Canonical equivalence is an attribute of a multilingual sort and describes how equivalent code point sequences are sorted. If canonical equivalence is applied in a particular linguistic sort, then canonically equivalent strings are treated as equal.
One Unicode code point can be equivalent to a sequence of base letter code points plus diacritic code points. This is called the Unicode canonical equivalence. For example, ä
equals its base letter a
and an umlaut. A linguistic flag, CANONICAL_EQUIVALENCE = TRUE
, indicates that all canonical equivalence rules defined in Unicode 4.0 need to be applied in a specific linguistic sort. Oracle-defined linguistic sorts include the appropriate setting for the canonical equivalence flag. You can set the flag to FALSE
to speed up the comparison and ordering functions if all the data is in its composed form.
For example, consider the following strings:
äa
(a
umlaut followed by a
)
aÞb
(a
followed by umlaut followed by b
)
äc
(a
umlaut followed by c
)
If CANONICAL_EQUIVALENCE=FALSE
, then the sort order of the strings is:
aÞb äa äc
This occurs because a
comes before ä
if canonical equivalence is not applied.
If CANONICAL_EQUIVALENCE=TRUE
, then the sort order of the strings is:
äa aÞb äc
This occurs because ä
and aÞ
are treated as canonically equivalent.
You can use Oracle Locale Builder to view the setting of the canonical equivalence flag in existing multilingual sorts. When you create a customized multilingual sort with Oracle Locale Builder, you can set the canonical equivalence flag as desired.
See Also: "Creating a New Linguistic Sort with the Oracle Locale Builder" for more information about setting the canonical equivalence flag |
In French, sorting strings of characters with diacritics first compares base letters from left to right, but compares characters with diacritics from right to left. For example, by default, a character with a diacritic is placed after its unmarked variant. Thus Èdit
comes before Edít
in a French sort. They are equal on the primary level, and the secondary order is determined by examining characters with diacritics from right to left. Individual locales can request that the characters with diacritics be sorted with the right-to-left rule. Set the REVERSE_SECONDARY
linguistic flag to TRUE
to enable reverse secondary sorting.
See Also: "Creating a New Linguistic Sort with the Oracle Locale Builder" for more information about setting the reverse secondary flag |
In Thai and Lao, some characters must first change places with the following character before sorting. Normally, these types of characters are symbols representing vowel sounds, and the next character is a consonant. Consonants and vowels must change places before sorting. Set the SWAP_WITH_NEXT
linguistic flag for all characters that must change places before sorting.
See Also: "Creating a New Linguistic Sort with the Oracle Locale Builder" for more information about setting theSWAP_WITH_NEXT flag |
Special letters is a term used in monolingual sorts. They are called expanding characters in multilingual sorts.
Special combination letters is the term used in monolingual sorts. They are called contracting letters in multilingual sorts.
One lowercase letter may map to multiple uppercase letters. For example, in traditional German, the uppercase letters for ß
are SS
.
These case conversions are handled by the NLS_UPPER
, NLS_LOWER
, and NLS_INITCAP
SQL functions, according to the conventions established by the linguistic sort sequence. The UPPER
, LOWER
, and INITCAP
SQL functions cannot handle these special characters, because their casing operation is based on binary mapping defined for the underlying character set, which is not linguistic sensitive.
The NLS_UPPER
SQL function returns all uppercase characters from the same character set as the lowercase string. The following example shows the result of the NLS_UPPER
function when NLS_SORT
is set to XGERMAN
:
SELECT NLS_UPPER ('große') "Uppercase" FROM DUAL; Upper ----- GROSSE
Operation inside an Oracle database is always sensitive to the case and the accents (diacritics) of the characters. Sometimes you may need to perform case-insensitive or accent-insensitive comparisons and sorts.
In previous versions of the database, case-insensitive queries could be achieved by using the NLS_UPPER
and NLS_LOWER
SQL functions. The functions change the case of strings based on a specific linguistic sort definition. This enables you to perform case-insensitive searches regardless of the language being used. For example, create a table called test1
as follows:
SQL> CREATE TABLE test1(word VARCHAR2(12)); SQL> INSERT INTO test1 VALUES('GROSSE'); SQL> INSERT INTO test1 VALUES('Große'); SQL> INSERT INTO test1 VALUES('große'); SQL> SELECT * FROM test1; WORD ------------ GROSSE Große große
Perform a case-sensitive search for GROSSE
as follows:
SQL> SELECT word FROM test1 WHERE word='GROSSE'; WORD ------------ GROSSE
Perform a case-insensitive search for GROSSE
using the NLS_UPPER
function:
SELECT word FROM test1 WHERE NLS_UPPER(word, 'NLS_SORT = XGERMAN') = 'GROSSE'; WORD ------------ GROSSE Große große
In Oracle Database 10g, Oracle provides case-insensitive and accent-insensitive options for linguistic sorts. Oracle provides the following types of monolingual and multilingual linguistic sorts:
Linguistic sorts that use information about base letters, diacritics, punctuation, and case. These are the standard monolingual and multilingual linguistic sorts that are described in "Using Linguistic Sorts".
Linguistic sorts that use information about base letters, diacritics, and punctuation. This type of sort is called case-insensitive.
Linguistic sorts that use information about base letters only. This type of sort is called accent-insensitive. (Accent is another word for diacritic.) An accent-insensitive sort is always case-insensitive as well.
The rest of this section contains the following topics:
The following examples show:
A sort that uses information about base letters, diacritics, punctuation, and case
A case-insensitive sort
An accent-insensitive sort
Example 5-1 Linguistic Sort Using Base Letters, Diacritics, Punctuation, and Case Information
The following list has been sorted using information about base letters, diacritics, punctuation, and case:
blackbird black bird black-bird Blackbird Black-bird blackbîrd bläckbird
Example 5-2 Case-Insensitive Linguistic Sort
The following list has been sorted using information about base letters, diacritics, and punctuation, ignoring case:
black bird black-bird Black-bird blackbird Blackbird blackbîrd bläckbird
black-bird
and Black-bird
have the same value in the sort, because the only different between them is case. They could appear interchanged in the list. Blackbird
and blackbird
also have the same value in the sort and could appear interchanged in the list.
Use the NLS_SORT
session parameter to specify a case-insensitive or accent-insensitive sort:
Append _CI
to an Oracle sort name for a case-insensitive sort.
Append _AI
to an Oracle sort name for an accent-insensitive and case-insensitive sort.
For example, you can set NLS_SORT
to the following types of values:
FRENCH_M_AI XGERMAN_CI
Binary sorts can also be case-insensitive or accent-insensitive. When you specify BINARY_CI
as a value for NLS_SORT
, it designates a sort that is accent-sensitive and case-insensitive. BINARY_AI
designates an accent-insensitive and case-insensitive binary sort. You may want to use a binary sort if the binary sort order of the character set is appropriate for the character set you are using.
For example, with the NLS_LANG
environment variable set to AMERICAN_AMERICA.WE8ISO8859P1
, create a table called test2
and populate it as follows:
SQL> CREATE TABLE test2 (letter VARCHAR2(10)); SQL> INSERT INTO test2 VALUES('ä'); SQL> INSERT INTO test2 VALUES('a'); SQL> INSERT INTO test2 VALUES('A'); SQL> INSERT INTO test2 VALUES('Z'); SQL> SELECT * FROM test2; LETTER ----------- ä a A Z
The default value of NLS_SORT
is BINARY
. Use the following statement to do a binary sort of the characters in table test2
:
SELECT * FROM test2 ORDER BY letter;
To change the value of NLS_SORT
, enter a statement similar to the following:
ALTER SESSION SET NLS_SORT=BINARY_CI;
The following table shows the sort orders that result from setting NLS_SORT
to BINARY
, BINARY_CI
, and BINARY_AI
.
BINARY | BINARY_CI | BINARY_AI |
---|---|---|
A |
a |
ä |
Z |
A |
a |
a |
Z |
A |
ä |
ä |
Z |
When NLS_SORT=BINARY
, uppercase letters come before lowercase letters. Letters with diacritics appear last.
When the sort considers diacritics but ignores case (BINARY_CI
), the letters with diacritics appear last.
When both case and diacritics are ignored (BINARY_AI
), ä
is sorted with the other characters whose base letter is a
. All the characters whose base letter is a
occur before z
.
You can use binary sorts for better performance when the character set is US7ASCII or another character set that has the same sort order as the binary sorts.
The following table shows the sort orders that result from German sorts for the table.
GERMAN | GERMAN_CI | GERMAN_AI |
---|---|---|
a |
a |
ä |
A |
A |
a |
ä |
ä |
A |
Z |
Z |
Z |
A German sort places lowercase letters before uppercase letters, and ä
occurs before Z
. When the sort ignores both case and diacritics (GERMAN_AI
), ä
appears with the other characters whose base letter is a
.
The examples in this section demonstrate a binary sort, a monolingual sort, and a multilingual sort. To prepare for the examples, create and populate a table called test3
. Enter the following statements:
SQL> CREATE TABLE test3 (name VARCHAR2(20)); SQL> INSERT INTO test3 VALUES('Diet'); SQL> INSERT INTO test3 VALUES('À voir'); SQL> INSERT INTO test3 VALUES('Freizeit');
Example 5-4 Binary Sort
The ORDER BY
clause uses a binary sort.
SQL> SELECT * FROM test3 ORDER BY name;
You should see the following output:
Diet Freizeit À voir
Note that a binary sort results in À voir
being at the end of the list.
Example 5-5 Monolingual German Sort
Use the NLSSORT
function with the NLS_SORT
parameter set to german
to obtain a German sort.
SQL> SELECT * FROM test3 ORDER BY NLSSORT(name, 'NLS_SORT=german');
You should see the following output:
À voir Diet Freizeit
Note that À voir
is at the beginning of the list in a German sort.
Example 5-6 Comparing a Monolingual German Sort to a Multilingual Sort
Insert the character string shown in Figure 5-1 into test
. It is a D
with a crossbar followed by ñ
.
Perform a monolingual German sort by using the NLSSORT
function with the NLS_SORT
parameter set to german
.
SQL> SELECT * FROM test2 ORDER BY NLSSORT(name, 'NLS_SORT=german');
The output from the German sort shows the new character string last in the list of entries because the characters are not recognized in a German sort.
Perform a multilingual sort by entering the following statement:
SQL> SELECT * FROM test2 ORDER BY NLSSORT(name, 'NLS_SORT=generic_m');
The output shows the new character string after Diet
, following ISO sorting rules.
When performing SQL comparison operations, characters are compared according to their binary values. A character is greater than another if it has a higher binary value. Because the binary sequences rarely match the linguistic sequences for most languages, such comparisons may not be meaningful for a typical user. To achieve a meaningful comparison, you can specify behavior by using the session parameters NLS_COMP
and NLS_SORT
. The way you set these two parameters determines the rules by which characters are sorted and compared.
The NLS_COMP
setting determines how NLS_SORT
is handled by the SQL operations. There are three valid values for NLS_COMP
:
BINARY
All SQL sorts and comparisons are based on the binary values of the string characters, regardless of the value set to NLS_SORT
. This is the default setting.
LINGUISTIC
All SQL sorting and comparison are based on the linguistic rule specified by NLS_SORT
. For example, NLS_COMP=LINGUISTIC
and NLS_SORT=BINARY_CI
means the collation sensitive SQL operations will use binary value for sorting and comparison but ignore character case.
ANSI
A limited set of SQL functions honor the NLS_SORT
setting. ANSI
is available for backward compatibility only. In general, you should set NLS_COMP
to LINGUISTIC
when performing linguistic comparison.
Table 5-2 shows how different SQL operations behave with these different settings.
Table 5-2 Linguistic Comparison Behavior with NLS_COMP Settings
BINARY | LINGUISTIC | ANSI | |
---|---|---|---|
SQL Operators |
|
|
|
|
Binary |
Honors |
Binary |
SQL Functions |
|
|
|
|
Binary |
Honors |
Binary |
|
Binary |
Honors |
Binary |
|
Binary |
Honors |
Binary |
|
Binary |
Honors |
Binary |
|
Honors |
Honors |
Honors |
|
Honors |
Honors |
Honors |
|
Honors |
Honors |
Honors |
|
Honors |
Honors |
Honors |
|
Binary |
Honors |
Binary |
|
Honors |
Honors |
Honors |
|
Honors |
Honors |
Honors |
|
Honors |
Honors |
Honors |
|
Honors |
Honors |
Honors |
|
Binary |
Honors |
Binary |
|
Binary |
Honors |
Binary |
|
Binary |
Honors |
Binary |
SQL Expressions |
|
|
|
|
Binary |
Honors |
Honors |
|
Binary |
Honors |
Honors |
|
Binary |
Honors |
Binary |
|
Binary |
Honors |
Binary |
|
Binary |
Honors |
Binary |
|
Binary |
Honors |
Binary |
|
Binary |
Honors |
Honors |
|
Binary |
Honors |
Honors |
|
Binary |
Honors |
Binary |
|
Honors |
Honors |
Honors |
|
Binary |
Honors |
Honors |
|
Binary |
Honors |
Binary |
See "NLS_COMP" and "NLS_SORT" for information regarding these parameters.
The following examples illustrate behavior with different NLS_COMP
settings.
SQL> ALTER SESSION SET NLS_COMP=BINARY; SQL> ALTER SESSION SET NLS_SORT=BINARY; SQL> SELECT ename FROM emp1; ENAME ---------------------- Mc Calla MCAfee McCoye Mccathye McCafeé 5 rows selected SQL> SELECT ename FROM emp1 WHERE ename LIKE 'McC%e'; ENAME ---------------------- McCoye 1 row selected
Example 5-8 Linguistic Comparison Binary Case-Insensitive Sort
The following illustrates behavior with a case-insensitive setting:
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC; SQL> ALTER SESSION SET NLS_SORT=BINARY_CI; SQL> SELECT ename FROM emp1 WHERE ename LIKE 'McC%e'; ENAME ---------------------- McCoye Mccathye 2 rows selected
Example 5-9 Linguistic Comparison Binary Accent-Insensitive Sort
The following illustrates behavior with an accent-insensitive setting:
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC; SQL> ALTER SESSION SET NLS_SORT=BINARY_AI; SQL> SELECT ename FROM emp1 WHERE ename LIKE 'McC%e'; ENAME ---------------------- McCoye Mccathye McCafeé 3 rows selected
Example 5-10 Linguistic Comparisons Returning Fewer Rows
Some operations may return fewer rows after applying linguistic rules. For example, with a binary setting, McAfee
and Mcafee
are different:
SQL> ALTER SESSION SET NLS_COMP=BINARY; SQL> ALTER SESSION SET NLS_SORT=BINARY; SQL> SELECT DISTINCT ename FROM emp2; ENAME ---------------------- McAfee Mcafee McCoy 3 rows selected
However, with a case-insensitive setting, McAfee
and Mcafee
are the same:
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC; SQL> ALTER SESSION SET NLS_SORT=BINARY_CI; SQL> SELECT DISTINCT ename FROM emp2; ENAME ---------------------- McAfee McCoy 2 rows selected
In this example, either McAfee
or Mcafee
could be returned from the DISTINCT
operation. There is no guarantee exactly which one will be picked.
Example 5-11 Linguistic Comparisons Using XSPANISH
There are cases where characters the are same using binary comparison but different using linguistic comparison. For example, with a binary setting, the character C
in Cindy
, Chad
, and Clara
represents the same letter C
:
SQL> ALTER SESSION SET NLS_COMP=BINARY; SQL> ALTER SESSION SET NLS_SORT=BINARY; SQL> SELECT ename FROM emp3 WHERE ename LIKE 'C%'; ENAME ---------------------- Cindy Chad Clara 3 rows selected
In a database session with the linguistic rule set to traditional Spanish, XSPANISH
, ch
is treated as one character. So the letter c
in Chad
is different than the letter C
in Cindy
and Clara
:
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC; SQL> ALTER SESSION SET NLS_SORT=XSPANISH; SQL> SELECT ename FROM emp3 WHERE ename LIKE 'C%'; ENAME ---------------------- Cindy Clara 2 rows selected
And the letter c
in combination ch
is different than the c
standing by itself:
SQL> SELECT REPLACE ('character', 'c', 't') "Changes" FROM DUAL; Changes --------------------- charatter
Linguistic sorting is language-specific and requires more data processing than binary sorting. Using a binary sort for ASCII is accurate and fast because the binary codes for ASCII characters reflect their linguistic order. When data in multiple languages is stored in the database, you may want applications to sort the data returned from a SELECT...ORDER BY
statement according to different sort sequences depending on the language. You can accomplish this without sacrificing performance by using linguistic indexes. Although a linguistic index for a column slows down inserts and updates, it greatly improves the performance of linguistic sorting with the ORDER
BY
clause.
You can create a function-based index that uses languages other than English. The index does not change the linguistic sort order determined by NLS_SORT
. The index simply improves the performance. The following statement creates an index based on a German sort:
CREATE TABLE my_table(name VARCHAR(20) NOT NULL); CREATE INDEX nls_index ON my_table (NLSSORT(name, 'NLS_SORT = German')); /*The NOT NULL in the CREATE TABLE statement ensures that the index is used*/
After the index has been created, enter a SELECT
statement similar to the following:
SELECT * FROM my_table ORDER BY name;
It returns the result much faster than the same SELECT
statement without an index.
The rest of this section contains the following topics:
There are three ways to build linguistic indexes for data in multiple languages:
Build a linguistic index for each language that the application supports. This approach offers simplicity but requires more disk space. For each index, the rows in the language other than the one on which the index is built are collated together at the end of the sequence. The following example builds linguistic indexes for French and German.
CREATE INDEX french_index ON employees (NLSSORT(employee_id, 'NLS_SORT=FRENCH')); CREATE INDEX german_index ON employees (NLSSORT(employee_id, 'NLS_SORT=GERMAN'));
Oracle chooses the index based on the NLS_SORT
session parameter or the arguments of the NLSSORT
function specified in the ORDER
BY
clause. For example, if the NLS_SORT
session parameter is set to FRENCH
, then Oracle uses french_index
. When it is set to GERMAN
, Oracle uses german_index
.
Build a single linguistic index for all languages. This requires a language column (LANG_COL
in "Example: Setting Up a French Linguistic Index") to be used as a parameter of the NLSSORT
function. The language column contains NLS_LANGUAGE
values for the data in the column on which the index is built. The following example builds a single linguistic index for multiple languages. With this index, the rows with the same values for NLS_LANGUAGE
are sorted together.
CREATE INDEX i ON t (NLSSORT(col, 'NLS_SORT=' || LANG_COL));
Queries choose an index based on the argument of the NLSSORT
function specified in the ORDER BY
clause.
Build a single linguistic index for all languages using one of the multilingual linguistic sorts such as GENERIC_M
or FRENCH_M
. These indexes sort characters according to the rules defined in ISO 14651. For example:
CREATE INDEX i on t (NLSSORT(col, 'NLS_SORT=GENERIC_M');
The following are requirements for using linguistic indexes:
This section also includes:
The NLS_SORT
parameter should indicate the linguistic definition you want to use for the linguistic sort. If you want a French linguistic sort order, then NLS_SORT
should be set to FRENCH
. If you want a German linguistic sort order, then NLS_SORT
should be set to GERMAN
.
There are several ways to set NLS_SORT
. You should set NLS_SORT
as a client environment variable so that you can use the same SQL statements for all languages. Different linguistic indexes can be used when NLS_SORT
is set in the client environment.
When you want to use the ORDER BY
column_name
clause with a column that has a linguistic index, include a WHERE
clause like the following:
WHERE NLSSORT(column_name) IS NOT NULL
This WHERE
clause is not necessary if the column has already been defined as a NOT NULL
column in the schema.
The following example shows how to set up a French linguistic index. You may want to set NLS_SORT
as a client environment variable instead of using the ALTER
SESSION
statement.
ALTER SESSION SET NLS_SORT='FRENCH'; CREATE INDEX test_idx ON test4(NLSSORT(name, 'NLS_SORT=FRENCH')); SELECT * FROM test4 ORDER BY col; ALTER SESSION SET NLS_COMP=LINGUISTIC; SELECT * FROM test4 WHERE name > 'Henri';
Note: The SQL functionsMAX( ) and MIN( ) , and also the LIKE operator, cannot use linguistic indexes when NLS_COMP is set to LINGUISTIC . |
Searching and sorting are related tasks. Organizing data and processing it in a linguistically meaningful order is necessary for proper business processing. Searching and matching data in a linguistically meaningful way depends on what sort order is applied. For example, searching for all strings greater than c
and less than f
produces different results depending on the value of NLS_SORT
. In an ASCII binary sort the search finds any strings that start with d
or e
but excludes entries that begin with upper case D
or E
or accented e
with a diacritic, such as ê
. Applying an accent-insensitive binary sort returns all strings that start with d
, D,
and accented e,
such as Ê
or ê
. Applying the same search with NLS_SORT
set to XSPANISH
also returns strings that start with ch
, because ch
is treated as a composite character that sorts between c
and d
in traditional Spanish. This chapter discusses the kinds of sorts that Oracle offers and how they affect string searches by SQL and SQL regular expressions.
Regular expressions provide a powerful method of identifying patterns of strings within a body of text. Usage ranges from a simple search for a string such as San Francisco
to the more complex task of extracting all URLs to finding all words whose every second character is a vowel. SQL and PL/SQL support regular expressions in Oracle Database 10g.
Traditional regular expression engines were designed to address only English text. However, regular expression implementations can encompass a wide variety of languages with characteristics that are very different from western European text. Oracle's implementation of regular expressions is based on the Unicode Regular Expression Guidelines. The REGEXP
SQL functions work with all character sets that are supported as database character sets and national character sets. Moreover, Oracle enhances the matching capabilities of the POSIX regular expression constructs to handle the unique linguistic requirements of matching multilingual data.
Oracle enhancements of the linguistic-sensitive operators are described in the following sections:
See Also:
|
According to the POSIX standard, a range in a regular expression includes all collation elements between the start point and the end point of the range in the linguistic definition of the current locale. Therefore, ranges in regular expressions are meant to be linguistic ranges, not byte value ranges, because byte value ranges depend on the platform, and the end user should not be expected to know the ordering of the byte values of the characters. The semantics of the range expression must be independent of the character set. This implies that a range such as [a-d]
includes all the letters between a
and d
plus all of those letters with diacritics, plus any special case collation element such as ch
in Traditional Spanish that is sorted as one character.
Oracle interprets range expressions as specified by the NLS_SORT
parameter to determine the collation elements covered by a given range. For example:
Expression: [a-d]e NLS_SORT: BINARY Does not match: cheremoya NLS_SORT: XSPANISH Matches: >>che<<remoya
This construct is introduced by the POSIX standard to separate collating elements. A collating element is a unit of collation and is equal to one character in most cases. However, the collation sequence in some languages may define two or more characters as a collating element. The historical regular expression syntax does not allow the user to define ranges involving multicharacter collation elements. For example, there was no way to define a range from a
to ch
because ch
was interpreted as two separate characters.
By using the collating element delimiter [. .]
, you can separate a multicharacter collation element from other elements. For example, the range from a
to ch
can be written as [a-[.ch.]]
. It can also be used to separate single-character collating elements. If you use [. .]
to enclose a multicharacter sequence that is not a defined collating element, then it is considered as a semantic error in the regular expression. For example, [.ab.]
is considered invalid if ab
is not a defined multicharacter collating element.
In English regular expressions, the range expression can be used to indicate a character class. For example, [a-z]
can be used to indicate any lowercase letter. However, in non-English regular expressions, this approach is not accurate unless a
is the first lowercase letter and z
is the last lowercase letter in the collation sequence of the language.
The POSIX standard introduces a new syntactical element to enable specifying explicit character classes in a portable way. The [: :]
syntax denotes the set of characters belonging to a certain character class. The character class definition is based on the character set classification data.
Oracle also supports equivalence classes through the [= =]
syntax as recommended by the POSIX standard. A base letter and all of the accented versions of the base constitute an equivalence class. For example, the equivalence class [=a=]
matches ä
as well as â
. The current implementation does not support matching of Unicode composed and decomposed forms for performance reasons. For example, ä (a
umlaut) does not match 'a
followed by umlaut'.
The following examples show regular expression matches.
Example 5-12 Case-Insensitive Match Using the NLS_SORT Value
Case sensitivity in an Oracle regular expression match is determined at two levels: the NLS_SORT
initialization parameter and the runtime match option. The REGEXP
functions inherit the case-sensitivity behavior from the value of NLS_SORT
by default. The value can also be explicitly overridden by the runtime match option 'c'
(case sensitive) or 'i'
(case insensitive).
Expression: catalog(ue)? NLS_SORT: GENERIC_M_CI Matches:
>>Catalog<< >>catalogue<< >>CATALOG<<
Oracle SQL syntax:
SQL> ALTER SESSION SET NLS_SORT='GENERIC_M_CI'; SQL> SELECT col FROM test WHERE REGEXP_LIKE(col,'catalog(ue)?');
Example 5-13 Case Insensitivity Overridden by the Runtime Match Option
Expression: catalog(ue)? NLS_SORT: GENERIC_M_CI Match option: 'c' Matches:
>>catalogue<<
Does not match:
Catalog CATALOG
Oracle SQL syntax:
SQL> ALTER SESSION SET NLS_SORT='GENERIC_M_CI'; SQL> SELECT col FROM test WHERE REGEXP_LIKE(col,'catalog(ue)?','c');
Example 5-14 Matching with the Collation Element Operator [..]
Expression: [^-a-[.ch.]]+ /*with NLS_SORT set to xspanish*/ Matches:
>>driver<<
Does not match:
cab
Oracle SQL syntax:
SQL> SELECT col FROM test WHERE REGEXP_LIKE(col,'[^-a-[.ch.]]+');
Example 5-15 Matching with the Character Class Operator [::]
This expression looks for 6-character strings with lowercase characters. Note that accented characters are matched as lowercase characters.
Expression: [[:lower:]]{6} Database character set: WE8ISO8859P1 Matches:
>>maître<< >>mòbile<< >>pájaro<< >>zurück<<
Oracle SQL syntax:
SQL> SELECT col FROM test WHERE REGEXP_LIKE(col,'[[:lower:]]{6}');
Example 5-16 Matching with the Base Letter Operator [==]
Expression: r[[=e=]]sum[[=e=]] Matches:
>>resume<< >>résumé<< >>résume<< >>resumé<<
Oracle SQL syntax:
SQL> SELECT col FROM test WHERE REGEXP_LIKE(col,'r[[=e=]]sum[[=e=]]');
See Also:
|