Oracle DECOMPOSE() Function

Oracle DECOMPOSE() is a built-in function that returns the result of applying a Unicode decomposition to the given argument.

Oracle `DECOMPOSE()` Syntax

Here is the syntax for the Oracle DECOMPOSE() function:

DECOMPOSE( string [, { 'CANONICAL' | 'COMPATIBILITY' } ] )

Parameters

string: Required.
{ 'CANONICAL' | 'COMPATIBILITY' }: Optional. If CANONICAL (case-insensitive), it applies canonical decomposition as defined in Unicode Standard Annex D68 and returns a string in the NFD normalized form. If COMPATIBILITY, it applies compatibility decomposition as defined in Unicode Standard Annex D65 and returns a string in the NFKD normalized form. CANONICAL is the default value.

Both parameters can be any data type from CHAR, VARCHAR2, NCHAR, or NVARCHAR2, or any other data type that can be implicitly converted to VARCHAR2 or NVARCHAR2.

Return Value

The Oracle DECOMPOSE() function returns the result of applying a Unicode decomposition to the given argument. The decomposition applied is determined by the second optional parameter. The return value of this function has the same character set as the first parameter.

If the character set of the first parameter is not one of the Unicode character sets, DECOMPOSE() returns the unmodified argument.

If any parameter is NULL, DECOMPOSE() returns NULL.

Oracle `DECOMPOSE()` Examples

Here are some examples that demonstrate the usage of the Oracle DECOMPOSE() function.

Basic Usage

The following example demonstrates the result of applying the Oracle DECOMPOSE() function to the character ö:

SELECT
    DECOMPOSE('ö')
FROM dual;

输出：

DECOMPOSE('ö')
_________________
ö

If you encounter garbled characters like ? on your terminal, you can get its ASCII version using ASCIISTR() as follows:

SELECT
    ASCIISTR(DECOMPOSE('ö'))
FROM dual;

输出：

ASCIISTR(DECOMPOSE('?'))
___________________________
o\0308

NULL Parameter

If any parameter is NULL, DECOMPOSE() returns NULL.

SET NULL 'NULL';
SELECT
    DECOMPOSE(NULL)
FROM dual;

输出：

DECOMPOSE(NULL)
__________________
NULL

In this example, we use the statement SET NULL 'NULL'; to display the NULL value as the string 'NULL'.

Conclusion