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_1||CHR(38)||string_2 |
CONCAT | The following two expressions are equivalent.
var := string_1||string_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. var := INITCAP('new york'), |
3.147.71.6