The following list includes common string functions that are used in PL/SQL.
ASCII
Returns the decimal representation of a number. The argument is a character. The following SELECT returns 65. The functions ASCII and CHR perform reverse functions.
SELECT ASCII('A') FROM dual;
CHR
Returns the character equivalent of a decimal number. The argument is a decimal number. The result is the ASCII character. The following returns the letter A:
SELECT CHR(65) FROM dual;
Suppose you want to include the ASCII character (&) in a PL/SQL program. This applies to SQL*Plus. Realizing that (&) is a SQL*Plus special character that denotes a command line argument, you first determine the decimal equivalent.
SQL> SELECT ASCII('&') FROM dual; 38
Knowing the decimal equivalent, you can code the following.
string_1CHR(38)string_2
CONCAT
The following two expressions are equivalent.
var := string_1string_2 var := concat(string_1, string_2)
The first form is the standard form for PL/SQL code. The CONCAT function is used in Pro*C code because the double pipe is compiled as a C operator.
GREATEST
Returns the largest value from a set of values. Expressions can be numbers , dates, or character strings.
var := GREATEST(var_1, var_2, var_3, etc);
LEAST
Returns the smallest value from a set.
var := LEAST(var_1, var_2, var_3, etc);
LENGTH
Returns the length of a string. This function works well in conjunction with INSTR, REPLACE, and TRANSLATE.
len := LENGTH('PL/SQL'); -- len equals 6
INITCAP
Returns a string in initial caps.
var := INITCAP('NEW YORK'); var equals 'New York'
LOWER
Returns a lower case string. The following returns: new york.
var := INITCAP('NEW YORK');
UPPER
Returns an upper case string. The following returns: NEW YORK.
