Oracle Regular Expressions Pocket Reference

Name

[. .] (Collation Element) — Specifies a collation element

Synopsis

Use [. and .] to enclose a collation element, usually a multicharacter collation element. Collation elements must be specified within bracket expressions.

The following example uses the collation element [.ch.] to find a word containing the Spanish letter 'ch' with a case-insensitive search. First, look at the results when we simply place the letters 'c' and 'h' in a bracket expression:

ALTER SESSION SET NLS_LANGUAGE=SPANISH;
SELECT REGEXP_SUBSTR(
   'El caballo, Chico come la tortilla.',
   '[[:alpha:]]*[ch][[:alpha:]]*',1,1,'i')
FROM dual;

caballo

These aren’t the results we want! Even though 'ch' is two letters, Spanish, at least old Spanish, treats it as one. Collation elements let us deal with this situation:

ALTER SESSION SET NLS_SORT=XSPANISH;
SELECT REGEXP_SUBSTR(
   'El caballo, Chico come la tortilla.',
   '[[:alpha:]]*[[.ch.]][[:alpha:]]*',1,1,'i')
FROM dual;

Chico

By specifying the collation [.ch.] in the bracket expression, we tell the regular expression engine to look for the combination 'ch', not for a 'c' or an 'h‘. We also had to change our NLS_SORT setting from SPANISH (the default for the Spanish language) to XSPANISH in order for the collation to be recognized. This is because SPANISH uses modern rules that treat 'ch' as two letters, but XSPANISH uses older rules that treat 'ch' as one letter.

Note

You cannot arbitrarily put any two letters in a collation. See Table 1-4.

Technically, any single character ...