Oracle PL/SQL Language Pocket Reference- P14

Oracle PL/SQL Language Pocket Reference- P14: This pocket guide features quick-reference information to help you use Oracles PL/SQL language. It includes coverage of PL/SQL features in the newest version of Oracle, Oracle8i. It is a companion to Steven Feuerstein and Bill Pribyls bestselling Oracle PL/SQL Programming. Updated for Oracle8, that large volume (nearly 1,000 pages) fills a huge gap in the Oracle market, providing developers with a single, comprehensive guide to building applications with PL/SQL and building them the right way. ...
Nội dung trích xuất từ tài liệu:
Oracle PL/SQL Language Pocket Reference- P14 Previous: III. Built-In Chapter 11 Next: 11.2 Character Functions Function Examples 11. Character Functions Contents: Character Function Descriptions Character Function Examples A character function is a function that takes one or more character values as parameters and returns either a character value or a number value. The Oracle Server and PL/SQL provide a number of different character datatypes, including CHAR, VARCHAR, VARCHAR2, LONG, RAW, and LONG RAW. In PL/SQL, the three different datatype families for character data are: VARCHAR2 A variable-length character datatype whose data is converted by the RDBMS CHAR The fixed-length datatype RAW A variable-length datatype whose data is not converted by the RDBMS, but instead is left in raw form When a character function returns a character value, that value is always of type VARCHAR2 (variable length), with the following two exceptions: UPPER and LOWER. These functions convert to upper- and lowercase, respectively, and return CHAR values (fixed length) if the strings they are called on to convert are fixed-length CHAR arguments. PL/SQL provides a rich set of character functions that allow you to get information about strings and modify the contents of those strings in very high-level, powerful ways. Table 11.1 shows the character functions covered in detail in this chapter. The remaining functions (not covered in this chapter) are specific to National Language Support and Trusted Oracle. Table 11.1: The Built-In Character FunctionsPlease purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Name Description ASCII Returns the ASCII code of a character. CHR Returns the character associated with the specified collating code. CONCAT Concatenates two strings into one. INITCAP Sets the first letter of each word to uppercase. All other letters are set to lowercase. INSTR Returns the location in a string of the specified substring. LENGTH Returns the length of a string. LOWER Converts all letters to lowercase. LPAD Pads a string on the left with the specified characters. LTRIM Trims the left side of a string of all specified characters. REPLACE Replaces a character sequence in a string with a different set of characters. RPAD Pads a string on the right with the specified characters. RTRIM Trims the right side of a string of all specified characters. SOUNDEX Returns the soundex of a string. SUBSTR Returns the specified portion of a string. TRANSLATE Translates single characters in a string to different characters. UPPER Converts all letters in the string to uppercase.Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 11.1 Character Function Descriptions The following sections briefly describe each of the PL/SQL character functions. 11.1.1 The ASCII function The ASCII function returns the NUMBER code that represents the specified character in the database character set. The specification of the ASCII function is: FUNCTION ASCII (single_character IN VARCHAR2) RETURN NUMBER where single_character is the character to be located in the collating sequence. Even though the function is named ASCII, it will return the code location in whatever the database character set is set to, such as EBCDIC Code Page 500 or 7-bit ASCII. For example, in the 7-bit ASCII character set, ASCII (`a) returns 97. Remember that the collating code for uppercase letters is different from that for lowercase letters. ASCII (`A) returns 65 (in the 7-bit ASCII character set) because the uppercase letters come before the lowercase letters in the sequence. If you pass more than one character in the parameter to ASCII, it returns the collating code for the first character and ignores the other characters. As a result, the following calls to ASCII all return the same value of 100: ASCII (defg) ==> 100 ASCII (d) ==> 100 ASCII (d_e_f_g) ==> 100 11.1.2 The CHR function The CHR function is the inverse of ASCII. It returns a VARCHAR2 character (length 1) that corresponds to the location in the collating sequence provided as a parameter. The specification of the CHR function is: FUNCTION CHR (code_location IN NUMBER) RETURN VARCHAR2 where code_location is the number specifying the location in the collating sequence. The CHR function is especially valuable when you need to make reference to a nonprintable character in your code. For example, the location in the standard ASCII collating sequence for the newline character is ten. The CHR function therefore gives me a way to search for the linefeed control character in a string, and perform operations on a string based on the presence of that control character. You can also insert a linefeed into a character string using the CHR function. Suppose I have to build a report that displays the address of a company. A company can have up to four address strings (in addition to city, state, and zipcode). I need to put each address string on a new line, but I dont want any blank lines embedded in the address. The following SELECT will not do the trick:Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. SELECT name, address1, address2, address3, address4, city || , || state || || zipcode location FROM company; Assuming each column (report field) goes on a new line, you will end up using six lines per address, no matter how many of these a ...