11.14. Miscellaneous String Functions
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'), |
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.