Oracle DECOMPOSE Function: A Simple Guide

The DECOMPOSE function in Oracle SQL is a specialized Unicode function. It does the opposite of COMPOSE. Its job is to take a single, pre-composed character (like ö or á) and break it down into its separate parts: the base character and its combining mark (like an umlaut or an accent).

This is an advanced function used for standardizing and normalizing Unicode text, often for technical comparisons or storage.

What is the DECOMPOSE Function in Oracle?

The DECOMPOSE(string, [method]) function takes a string and returns its Unicode decomposition.

For example, in Unicode, the character ö can be represented in two ways:

1. As a single, pre-composed character (ö).
2. As two characters: a normal o followed by a "combining umlaut" mark (\0308).

DECOMPOSE is the function that converts the first form into the second.

• method (Optional): You can specify 'CANONICAL' (default) or 'COMPATIBILITY'. For most uses, you can omit this and use the default.

DECOMPOSE Function Syntax

The syntax for DECOMPOSE is:

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

Let's break that down:

• string: The string or column containing the pre-composed characters.
• 'CANONICAL' (Default): The standard decomposition.
• 'COMPATIBILITY': A more aggressive decomposition.

Oracle DECOMPOSE Function Examples

Here are two practical examples of how to use DECOMPOSE.

(Note: The visual output of DECOMPOSE can sometimes look identical to the input (e.g., 'ö' still looks like 'ö') because many SQL clients will auto-re-compose the characters for display. Under the hood, however, the string is now two separate characters.)

Example 1: Decomposing 'ö' (o-umlaut) using DECOMPOSE

This example takes the single character ö and breaks it into 'o' plus its combining mark.

Query:

SELECT 
  DECOMPOSE('ö') AS "Decomposed_Character"
FROM DUAL;

Result: (Visually, this will likely still appear as ö, but it is now stored as 'o' followed by the combining umlaut character \0308)

Decomposed_Character
--------------------
ö

Example 2: Verifying the Decomposed Length using DECOMPOSE

This example better shows what DECOMPOSE is doing. We will compare the LENGTH of the original character ('á') with the LENGTH of its decomposed version.

The original á has a length of 1. The decomposed version (a + ´) will have a length of 2.

Query:

SELECT 
  LENGTH('á') AS "Original_Length",
  LENGTH(DECOMPOSE('á')) AS "Decomposed_Length"
FROM DUAL;

Result:

Original_Length Decomposed_Length
--------------- -----------------
              1                 2

This confirms that DECOMPOSE successfully broke the single character á into its two component parts.