Oracle TO_SINGLE_BYTE Function: A Simple Guide

The TO_SINGLE_BYTE function in Oracle SQL is a specialized character set function. It is the direct opposite of TO_MULTI_BYTE. Its job is to convert multibyte (or "full-width") characters into their corresponding single-byte (or "half-width") characters.

This function is primarily used in databases that support character sets (like UTF8) that contain both single-byte and multibyte versions of the same character. This is common in East Asian language character sets.

What is the TO_SINGLE_BYTE Function in Oracle?

The TO_SINGLE_BYTE(char) function takes a string and returns a new string where any multibyte characters that have a single-byte equivalent are converted.

For example, many character sets have two versions of the letter 'A':

1. Multibyte (Full-width): 'Ａ' (a wider version)
2. Single-byte (Half-width): 'A' (the standard ASCII character)

TO_SINGLE_BYTE converts the first form to the second. If a multibyte character has no single-byte equivalent, it is returned unchanged.

TO_SINGLE_BYTE Function Syntax

The syntax for TO_SINGLE_BYTE is very simple:

TO_SINGLE_BYTE(char)

Let's break that down:

• char: The string or column you want to convert.

Oracle TO_SINGLE_BYTE Function Examples

Here are two practical examples of how to use TO_SINGLE_BYTE.

Example 1: Converting a Full-Width Character using TO_SINGLE_BYTE

This example converts the full-width (multibyte) 'Ａ' character into the standard single-byte 'A'. We use UNISTR to provide the Unicode code for the full-width version.

Query:

SELECT 
  UNISTR('\FF21') AS "Full_Width_A",
  TO_SINGLE_BYTE(UNISTR('\FF21')) AS "Single_Byte_A"
FROM DUAL;

Result:

Full_Width_A Single_Byte_A
------------ -------------
Ａ            A

Example 2: Verifying the Byte Change using TO_SINGLE_BYTE and DUMP

This example proves the conversion is happening by using DUMP to show the internal byte representation. The multibyte character will be several bytes long, while the converted single-byte character will be just one byte.

Query:

-- UNISTR('\FF21') is the full-width 'Ａ'
SELECT 
  DUMP(UNISTR('\FF21')) AS "Multi_Byte_Dump",
  DUMP(TO_SINGLE_BYTE(UNISTR('\FF21'))) AS "Single_Byte_Dump"
FROM DUAL;

Result: (The exact byte values depend on your database character set, but the length will change)

Multi_Byte_Dump            Single_Byte_Dump
-------------------------- ---------------------
Typ=1 Len=3: 239,188,161   Typ=96 Len=1: 65

The Len=3 shows the original 'Ａ' was 3 bytes. The Len=1 (with a value of 65, the ASCII code for 'A') shows it was successfully converted to a single byte.