Page 60
This function is used to pad the left side of a string with characters. These characters may also include white space (space bar). It produces a string of the length specified in the argument. Listing 3.28 shows an example of the Lpad function.
Listing 3.28L_03_28.TXTExample of the Use of the LPAD Function
SQL> select first_name, lpad(first_name, 15, ` `) 2 from employee; FIRST_NAME LPAD(FIRST_NAME --------------- --------------- CALVIN CALVIN LYNDON LYNDON RONALD RONALD GEORGE GEORGE ANDREW ANDREW . 19 records selected
Notice that the values in the second column now have white space and the string is shifted to the right. The displayed column fills the entire 15 characters.
The ltrim function trims occurrences of unwanted characters from the left side of the character string. In Listing 3.29, the `ROO' is trimmed from all the selected columns. The function removes characters from the left of the string until it reaches the first character not in the set. In Listing 3.2, the third character "O" in the name Roosevelt was also removed even though only two characters were specified in the SELECT statement. When Oracle7 performs the ltrim function, it looks at the first character in the function character list and trims all leading characters until the function encounters a different character in the string. The function then begins trimming the next specified character. It stops trimming when it reaches the end of the trim list and the leading character in the string does not match the specification. The second "O" in the string was trimmed because it matched the second character in the trim list.
Listing 3.29L_03_29.TXTExample of the Ltrim Function
SQL> select last_name, ltrim(last_name, `RO') 2 from employee 3 where last_name = `ROOSEVELT'; LAST_NAME LTRIM(LAST_NAME --------------- --------------- ROOSEVELT SEVELT ROOSEVELT SEVELT ROOSEVELT SEVELT 3 rows selected.
Page 61
Rpad is used to add characters to the right side of a string up to the specified length of the field. In Listing 3.30, dashes (-) are placed on the right side of the last_name field.
Listing 3.30L_03_30.TXTExample of the Rpad Function
SQL> select first_name, rpad(first_name, 15, `-`) 2 from employee; FIRST_NAME RPAD(FIRST_NAME --------------- --------------- CALVIN CALVIN--------- LYNDON LYNDON--------- RONALD RONALD--------- GEORGE GEORGE--------- ANDREW ANDREW--------- WILLIAM WILLIAM------- . 19 records selected.
This function is used to trim unwanted characters from the right side of a string. Columns that have null characters will not compare to columns with white space. The reason is that white space and null characters are actually two distinct characters. Another use for the rtrim function is to get rid of unwanted characters when concatenating columns(see Listing 3.31).
NOTE |
I often use this function when I need to compare columns from two tables, and I am concerned that one of the columns contains white space.n |
Listing 3.31L_03_31.TXTExample of the Rtrim Functions
SQL> select last_name||','||first_name, 2 rtrim(last_name, ` `)||','||first_name 3 from employee; LAST_NAME||','||FIRST_NAME RTRIM(LAST_NAME,'')||','||FIRST ------------------------------- ------------------------------- COOLIDGE ,CALVIN COOLIDGE,CALVIN JOHNSON ,LYNDON JOHNSON,LYNDON REAGAN ,RONALD REAGAN,RONALD BUSH ,GEORGE BUSH,GEORGE JOHNSON ,ANDREW JOHNSON,ANDREW . 19 records selected.
Page 62
The substr function selects a set of characters from a column character string. The function has three parameters: column name, beginning position, and number of characters to select or extract from the returned string. Oracle7 will only return the characters that match the defined parameter. The other characters in the column string will be discarded. This is a very common function to use. It is also very common for a SQL statement to use this function in the where clause to limit the number of records selected. Listing 3.32 shows an example of this.
Listing 3.32L_03_32.TXTExample of the Substr Function
SQL> select last_name, first_name, 2 birth_date, substr(to_char(birth_date), 4,3) 3 from employee 4 where substr(to_char(birth_date), 4,3) = `JAN'; LAST_NAME FIRST_NAME BIRTH_DAT SUB --------------- --------------- --------- --- FORD GERALD 09-JAN-13 JAN ROOSEVELT FRANKLIN 30-JAN-82 JAN 2 records selected.
The preceding statement produces a listing of employees that were born in January. It uses the substr function to extract the month from the birthdate column because the database does not have a column for month. The function extracts three characters from the date string, beginning at position four. You might notice that the example has one function nested within another. It is permissible to nest functions. To_char is a numeric function that converts numbers to characters. Because birth_date is defined as a date that is really a specially formatted number, it was necessary to convert the column to a character value.
Decode is a function that you can use on both numeric and column values. It is placed in the select clause and used to translate column values to another value. Databases often have columns that contain coded values that need to be translated for the user. The reason for the codes are to eliminate the amount of typing a user must perform to enter the data. The example tables use codes to denote the department name. `POL,' for example, represents `Political Science' and `WEL' represents `Welfare.' The decode function enables you to replace these codes with the more descriptive values. The syntax is: decode(column name, old_value_n1, new_value_n1). Listing 3.33 shows an example of how to use the decode function.
Listing 3.33L_03_33.TXTExample of the Decode Function
SQL> select last_name, first_name, fk_department, 2 decode(fk_department, `INT `, `INTERIOR', `POL `, 3 `POLITICAL SCIENCE', `WEL `, `WELFARE', `UNKNOWN') 4 from employee
Page 63
5 order by 3; LAST_NAME FIRST_NAME FK_D DECODE(FK_DEPARTM --------------- --------------- ---- ----------------- COOLIDGE CALVIN INT INTERIOR BUSH GEORGE INT INTERIOR FORD GERALD INT INTERIOR TRUMAN HAROLD INT INTERIOR EISENHOWER DWIGHT INT INTERIOR ROOSEVELT THEODORE INT INTERIOR JOHNSON LYNDON POL POLITICAL SCIENCE CLINTON WILLIAM POL POLITICAL SCIENCE . 19 rows selected.
This statement changes the values in the department column. You might notice that the last value in the function does not contain a code. This tells SQL*PLUS to place the value of `UNKNOWN' in the listing if the column value it encounters is missing or is not contained in the list. The order by clause uses a column number rather the than column. The reason is the column name changes to the entire decode expression unless an alias is recorded. An alias was not specified, so it was easier to enter a column number than the decode expression.
SQL*PLUS contains a variety of numeric functions that enable the developer to perform computations as necessary. Table 3.4 contains a listing of these functions:
Table 3.4Numeric Functions
Function Name | Syntax | Description |
+ | value1 + value2 | Addition |
_ | value2 _ value4 | Subtraction |
* | value5 * value6 | Multiplication |
/ | value7/value8 | Division |
ABS | ABS(value) | Absolute value |
CEIL | CEIL(value) |
Smallest integer greater than or equal to the value |
COS | COS(value) | Cosine |
COSH | COSH(value) | Hyperbolic cosine of the value |
EXP | EXP(value) | e Raised to the valueth power |
continues