Oracle TO_SINGLE_BYTE() Function

Oracle TO_SINGLE_BYTE() is a built-in function that converts all multi-byte characters in the given argument to their corresponding single-byte characters and returns a value of the same data type as the argument.

TO_SINGLE_BYTE() is the opposite of TO_MULTI_BYTE().

Oracle `TO_SINGLE_BYTE()` Syntax

Here is the syntax of the Oracle TO_SINGLE_BYTE() function:

TO_SINGLE_BYTE(char)

Parameters

char: Required. It can be of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type.

Return Value

The Oracle TO_SINGLE_BYTE() function converts all multi-byte characters in the given argument to their corresponding single-byte characters and returns a value of the same data type as the argument.

If the single-byte characters in char do not have corresponding multi-byte characters, they will appear in the output string in their single-byte form. This function is useful only when the database character set includes both single-byte and multi-byte characters.

This function does not directly support CLOB data, but it can be passed as a parameter through implicit data conversion.

If any argument is NULL, TO_SINGLE_BYTE() returns NULL.

Oracle `TO_SINGLE_BYTE()` Examples

Here are several examples that demonstrate the usage of the Oracle TO_SINGLE_BYTE() function.

Basic Usage

The following example demonstrates how to convert the multi-byte character Ａ from UTF8 to the single-byte ASCII A:

SELECT TO_SINGLE_BYTE('Ａ')
FROM dual;

输出：

TO_SINGLE_BYTE('Ａ')
______________________
A

Let’s make it more obvious with the DUMP() function:

SELECT
  DUMP('Ａ'),
  DUMP(TO_SINGLE_BYTE('Ａ'))
FROM dual;

输出：

DUMP('Ａ')                    DUMP(TO_SINGLE_BYTE('Ａ'))
____________________________ ____________________________
Typ=96 Len=3: 239,188,161    Typ=1 Len=1: 65

The type ID of 'Ａ' is 96, and the length is 3. The returned value of TO_SINGLE_BYTE('Ａ') has a type ID of 1 and a length of 1.

NULL Parameter

If any argument is NULL, TO_SINGLE_BYTE() returns NULL.

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

输出：

TO_SINGLE_BYTE(NULL)
______________________
NULL

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

Conclusion

The Oracle TO_SINGLE_BYTE() is a built-in function that converts all multi-byte characters in the given argument to their corresponding single-byte characters and returns a value of the same data type as the argument.

Oracle TO_SINGLE_BYTE() Syntax