Tài liệu Oracle PLSQL Language- P14 ppt

Previous: III. Built-In

Functions

Chapter 11 Next: 11.2 Character

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 Functions

Please 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 don't 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 address strings are NULL. For example:

HAROLD HENDERSON

22 BUNKER COURT

SUITE 100

WYANDANCH, MN 66557

You can use the CHR function to suppress these internal blank lines as follows:

SELECT name ||

DECODE (address1, NULL, NULL, CHR (10) || address1) ||

DECODE (address2, NULL, NULL, CHR (10) || address2) ||

DECODE (address3, NULL, NULL, CHR (10) || address3) ||

DECODE (address4, NULL, NULL, CHR (10) || address4) ||

CHR (10) ||

city || ', ' || state || ' ' || zipcode

FROM company;

Now the query returns a single formatted column per company. The DECODE statement offers IF-THEN

logic within SQL and executes as follows: "If the address string is NULL then concatenate NULL;

otherwise insert a linefeed character. Then concatenate the address string."

In this way, blank address lines are ignored. If I now use Wrap on the report field which holds this string,

the address will be scrunched down to:

HAROLD HENDERSON

22 BUNKER COURT

SUITE 100

WYANDANCH, MN 66557

11.1.3 The CONCAT function

The CONCAT function concatenates by taking two VARCHAR2 strings and returning those strings

appended together in the order specified. The specification of the CONCAT function is:

FUNCTION CONCAT (string1 IN VARCHAR2, string2 IN VARCHAR2)

RETURN VARCHAR2

CONCAT always appends string2 to the end of string1. If either string is NULL, CONCAT returns the

non-NULL argument all by its lonesome. If both strings are NULL, CONCAT returns NULL. Here are

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

some examples of uses of CONCAT:

CONCAT ('abc', 'defg') ==> 'abcdefg'

CONCAT (NULL, 'def') ==> 'def'

CONCAT ('ab', NULL) ==> 'ab'

CONCAT (NULL, NULL) ==> NULL

I have a confession to make about CONCAT: I have never used it once in all my years of PL/SQL coding.

In fact, I never even noticed it was available until I did the research for this book. How can this be? Did I

never have to concatenate strings together in my programs? No, I certainly have performed many acts of

concatenation in my time. Surprisingly, the answer is that PL/SQL (and the Oracle RDBMS) offers a

second concatenation operator -- the double vertical bars (||). This operator is much more flexible and

powerful and is easier to use than CONCAT.

11.1.4 The INITCAP function

The INITCAP function reformats the case of the string argument, setting the first letter of each word to

uppercase and the remainder of the letters in that word to lowercase. A word is a set of characters

separated by a space or nonalphanumeric characters (such as # or _ ). The specification of INITCAP is:

FUNCTION INITCAP (string_in IN VARCHAR2) RETURN VARCHAR2

Here are some examples of the impact of INITCAP on your strings:

● Shift all lowercase to mixed case:

INITCAP ('this is lower') ==> 'This Is Lower'

● Shift all uppercase to mixed case:

INITCAP ('BIG>AND^TALL') ==> 'Big>And^Tall'

● Shift a confusing blend of cases to consistent initcap format:

INITCAP ('wHatISthis_MESS?') ==> 'Whatisthis_Mess?'

● Create Visual Basic-style variable names (I use REPLACE, explained later, to strip out the

embedded spaces).

REPLACE (INITCAP ('ALMOST_UNREADABLE_VAR_NAME'), '_',

NULL)

==>

'AlmostUnreadableVarName'

When and why would you use INITCAP? Many Oracle shops like to store all character string data in the

database, such as names and addresses, in uppercase. This makes it easier to search for records that match

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.