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ò';
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:
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.