Oracle PL/SQL Programming, Third Edition

Name

TO_MULTI_BYTE

Synopsis

TO_MULTI_BYTE translates single-byte characters to their multibyte equivalents. Some multibyte character sets, notably UTF-8, provide for more than one representation of a given character. In UTF-8, for example, letters such as “G” can be represented using one byte or using three bytes. TO_MULTI_BYTE lets you convert between the two representations. The function specification is as follows:

FUNCTION TO_MULTI_BYTE (string IN VARCHAR2) RETURN VARCHAR2

The datatype of the input value that you pass to TO_MULTI_BYTE determines the output datatype. The output datatype will always match the input datatype.

Following is an example of TO_MULTI_BYTE being used to convert the letter G into its multibyte representation. This example was generated on a system using UTF-8 as the national character set.

DECLARE
   g_one_byte NVARCHAR2 (1 CHAR) := 'G';
   g_three_bytes NVARCHAR2 (1 CHAR);
   g_one_again NVARCHAR2(1 CHAR);
   dump_output VARCHAR2(30);
BEGIN
   --Convert single-byte "G" to its multibyte representation
   g_three_bytes := TO_MULTI_BYTE(g_one_byte);
   DBMS_OUTPUT.PUT_LINE(LENGTHB(g_one_byte));
   DBMS_OUTPUT.PUT_LINE(LENGTHB(g_three_bytes));
   SELECT DUMP(g_three_bytes) INTO dump_output FROM dual;
   DBMS_OUTPUT.PUT_LINE(dump_output);

   --Convert that multibyte representation back to a single byte
   g_one_again := TO_SINGLE_BYTE(g_three_bytes);
   DBMS_OUTPUT.PUT_LINE(g_one_again || ' is ' ||
                        TO_CHAR(LENGTHB(g_one_again)) 
                        || ' byte again.');
END;

The output is:

1 3 Typ=1 Len=3: 239,188,167 G is ...