Previous | Table of Contents | Next

Page 60

The Lpad Function

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.TXT—Example 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

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.TXT—Example 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

The Rpad Function

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.TXT—Example 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.

The Rtrim Function

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.TXT—Example 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

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.TXT—Example 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.

Using the Decode Function

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.TXT—Example 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.

Using Number Functions

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

Previous | Table of Contents | Next