In Oracle Database, the DECOMPOSE() function returns the result of applying one of the Unicode decompositions to its string argument. The decomposition is determined by the second (optional) argument.

It’s the opposite of the COMPOSE() function.

Syntax

The syntax goes like this:

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

Where both arguments can be any of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data types, or a type that can be implicitly converted to either VARCHAR2 or NVARCHAR2.

Regarding the (optional) second argument:

• CANONICAL results in canonical decomposition, as described in the Unicode Standard definition D68, and returns a string in the NFD normalisation form.
• COMPATIBILITY results in compatibility decomposition, as described in the Unicode Standard definition D65, and returns a string in the NFKD normalisation form.

The default behaviour is to apply the canonical decomposition.

Example

Here’s an example:

SELECT DECOMPOSE('ã') FROM DUAL;

Result:

ã

However, the following example is a better demonstration:

SELECT ASCIISTR(DECOMPOSE('ã')) FROM DUAL;

Result:

a\0303

Here, we used ASCIISTR() to return an ASCII version of the result of DECOMPOSE() in the database character set.

Here’s what happens if we use ASCIISTR() without DECOMPOSE():

SELECT ASCIISTR('ã') FROM DUAL;

Result:

\00E3

Non Unicode Characters

If the character set of the argument is not one of the Unicode character sets, the argument is returned unmodified.

Example:

SELECT ASCIISTR(DECOMPOSE('a')) FROM DUAL;

Result:

a

Null Argument

If the argument is null, the result is null:

SET NULL 'null';
SELECT DECOMPOSE(null)
FROM DUAL;

Result:

null

By default, SQLcl and SQL*Plus return a blank space whenever a null value occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Invalid Argument Count

Calling DECOMPOSE() without any arguments results in an error:

SELECT DECOMPOSE()
FROM DUAL;

Result:

SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"

And passing too many arguments also results in an error:

SELECT DECOMPOSE('a', 'b')
FROM DUAL;

Result:

Error report -
ORA-12702: invalid NLS parameter string used in SQL function