PLSQL | CONVERT Function

The string in PL/SQL is actually a sequence of characters with an optional size specification.
The characters could be numeric, letters, blank, special characters or a combination of all.
The CONVERT function in PLSQL is used to convert a string from one character set to another.
Generally, the destination character set contains a representation of all the characters defined in the source character set.
If in any case, a character does not exist in the destination character set, a replacement character appears. These replacement characters can be defined as part of a character set definition.

Syntax:

CONVERT( string1, char_set_to [, char_set_from] )

Parameters Used –

1. string1 –
   It is used to specify the string to be converted. It can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
2. char_set_to –
   It is used to specify the character set to which the string needs to be converted.
3. char_set_from –
   It is an optional parameter which is used to specify the character set from which the string needs to be converted.

Note – Both the destination and source character set arguments can be either literals or columns containing the name of the character set.

Available Character Sets:

• US7ASCII : US 7-bit ASCII character set
• WE8DEC : West European 8-bit character set
• WE8HP : HP West European Laserjet 8-bit character set
• F7DEC : DEC French 7-bit character set
• WE8EBCDIC500 : IBM West European EBCDIC Code Page 500
• WE8PC850 : IBM PC Code Page 850
• WE8ISO8859P1 : ISO 8859-1 West European 8-bit character set

Supported Versions of Oracle/PLSQL:

1. Oracle 12c
2. Oracle 11g
3. Oracle 10g
4. Oracle 9i
5. Oracle 8i

Example:

DECLARE 
   Test_String string(10) := 'A B C D';
   Test_String2 string(20) := 'E Ä Ê Í';
   
BEGIN 
   dbms_output.put_line(CONVERT(Test_String, 'US7ASCII', 'WE8ISO8859P1')); 
   dbms_output.put_line(CONVERT(Test_String2, 'US7ASCII')); 
   
END;  

Output:

A B C D
E A E I