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_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.

var := INITCAP('new york');



Programming Oracle Triggers and Stored Procedures
Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)
ISBN: 0130850330
EAN: 2147483647
Year: 2003
Pages: 111
Authors: Kevin Owens

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net