SQL use of functions - character functions

Speaker notes

Slide 1:

In SQL, the developer can use functions to manipulate, modify, format, convert or calculate data.

This slide show focuses on character functions.

Note: The slide show does not cover all of the information covered in the notes - please be sure to read the notes!

Slide 2:

Character functions operate on individual rows and either convert or manipulate data.

In this example I am using the INITCAP function with the field that I want it to operate on enclosed in parenthesis.

Slide 3:

DUAL or SYSDUAL is a system file that contains a 1 character dummy field. If you want to try a select without using a table as I did in the second example, dual or sysdual in the FROM clause works.

Slide 4:

Note that this is not converting data on the table. It is converting for display purposes.

Slide 5:

Note that this is not converting data on the table. It is converting for display purposes.

Slide 6:

Functions are frequently used in this manner to maximize your chance of getting a match when you are not sure of the data on the table.

Note that this could have also been done the following ways:

LOWER(jobcode) = 'ci'

INITCAP(jobcode) = 'Ci'

Here I am assuming most of the rows/records have CI so the test against uppercase of CI makes sense.

Slide 7:

VARCHAR2 looks at the data in the field to determine length, CHAR pads with spaces to achieve the stated length.

Slide 8:

RTRIM trims spaces to the right.

LTRIM trims spaces to the left.

Slide 9:

NOTE the header over the first CIS50 in quotes.

Slide 10:

You frequently see * fill or * padding with numeric data on checks to assure that the amount can not be altered.

The format is LPAD/RPAD(the name of the field to be padded, the total length of the field, the character to use for padding).

Slide 11:

SUBSTR us a very powerful tool for extracting information from a string of data. Other powerful tools are INSTR, LENGTH and REPLACE. Together these allow for excellent data manipulation capability.

Slide 12:

INSTR looks for a particular character in the column/field and returns the location.

Slide 13:

The fields used here were described as VARCHAR2 so there is no padding of spaces to use the whole field.

Slide 14:

Notice the difference between the LENGTH when used on a VARCHAR2 column/field and when used on a CHARcolumn/ field.

Slide 15:

Finally, this slide shows the length used with numeric fields.

Slide 16:

These functions can be combined to manipulate data as shown in the next slide.

Slide 17:

This code uses the SUBSTR function to extract parts of the date and concatenate them back together in a different order. Comma and spaces are added.

Slide 18:

In this example, I am creating a code that is made up of parts of a variety of fields. This technique could be used to calculate an item number or some other code.

When two functions are used, the inner function is resolved first.