A few basical string functions

When we speak of strings in PL/SQL, we usually refer to the VARCHAR2 datatype, a variable length string, actually, with a fixed maximum size. Alternatively we could use something more similar to an array of char, described by the datatype CHAR, better used when we need just one character, in that case we could use the datatype name, CHAR, without specifying its size.

Here is the declaration of three different string variables:

l_str varchar2(80); -- up to 80 chars
l_cstr char(80); -- exactely 80 chars
l_char char; -- a single char

A string could be defined delimiting a bunch of characters with in couple of single quotes:

l_str := 'Però al Perù parò';
dbms_output.put_line(l_str);

The actual characters you use in your strings depend on the database language settings.

To define a unicode string, we prefix the opening single quote with a 'u'. In a unicode string we could put also the hexadecimal internal representation for (special) characters:

l_str := u'ein gro\00DFes Bier, bitte';

In this case we use the numerical representation for the german ess-zed (aka sharp s, 'ß').

If we are interested in which is the internal representation for a given character, we can use the ASCII() function:

dbms_output.put_line('ß has internal representation ' || ascii('ß'));

Reversely, we can use the CHR() function to convert a number in the character it represents. This is expecially useful for the ampersand character ('&' represented by a decimal 38) that is used as special escape character in SQL*PLUS to substitute variable with user input:

dbms_output.put_line('Welcome to the AT' || chr(38) || 'T Portal!');

Common conversion strings functions are the one that give us an all-lowercase or uppercase version of our string:

dbms_output.put_line(lower(l_str));
dbms_output.put_line(upper(l_str));
dbms_output.put_line(initcap(l_str));

The last one, INITCAP(), is less common, and less useful. It converts the input string in a all-lowercase one, but the initials of each word, that are made uppercase.

Chapter 8 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about strings datatypes.