SQL Use of Functions Character functions Please use

SQL Use of Functions Character functions Please use speaker notes for additional information!

Character functions PAY_ ---1111 2222 3333 4444 5555 6666 NAME ----------Linda Costa John Davidson Susan Ash Stephen York Richard Jones Joanne Brown JO -CI IN AP CM CI IN STARTDATE SALARY BONUS ---------15 -JAN-97 45000 1000 25 -SEP-92 40000 1500 05 -FEB-00 25000 500 03 -JUL-97 42000 30 -OCT-92 50000 2000 18 -AUG-94 48000 2000 6 rows selected. SQL> SELECT name, INITCAP(jobcode) 2 FROM first_pay; NAME ----------Linda Costa John Davidson Susan Ash Stephen York Richard Jones Joanne Brown 6 rows selected. IN -Ci In Ap Cm Ci In INITCAP is a function that shows the data converted to an initial capital followed by lower case. In this example, jobcode was originally in uppercase. Now there is an initial capital followed by a lower case letter. Note: The column header now says IN. This would be an appropriate time for an alias.

Character functions SQL> SELECT INITCAP(startdate) 2 FROM first_pay; On the table, the months are stored in uppercase, here they are shown with an initial capital followed by lower case. In this example, you can clearly see the function included in the column header. INITCAP(STARTDATE) -----------------15 -Jan-97 25 -Sep-92 05 -Feb-00 03 -Jul-97 30 -Oct-92 SQL> SELECT INITCAP('mrs. grocer') 2 FROM dual; INITCAP('MR -----Mrs. Grocer I am using the function to show particular words with initial capitals.

Character functions SQL> SELECT UPPER(name) 2 FROM first_pay; Originally the data was in mixed case, the UPPER function converts it to UPPER case for this display. UPPER(NAME) ----------LINDA COSTA JOHN DAVIDSON SUSAN ASH STEPHEN YORK RICHARD JONES JOANNE BROWN Listing of the table after the UPPER function shows the data unaffected. SQL> SELECT * 2 FROM first_pay; PAY_ ---1111 2222 3333 4444 5555 6666 NAME ----------Linda Costa John Davidson Susan Ash Stephen York Richard Jones Joanne Brown JO -CI IN AP CM CI IN STARTDATE SALARY BONUS ---------15 -JAN-97 45000 1000 25 -SEP-92 40000 1500 05 -FEB-00 25000 500 03 -JUL-97 42000 30 -OCT-92 50000 2000 18 -AUG-94 48000 2000

Character functions SQL> SELECT LOWER(name), LOWER(jobcode) 2 FROM first_pay; LOWER(NAME) ----------linda costa john davidson susan ash stephen york richard jones joanne brown LO -ci in ap cm ci in The LOWER function converts fields to lower case for display. NAME was originally in mixed case and jobcode was originally in upper case.

Character functions SQL> SELECT * 2 FROM first_pay 3 WHERE UPPER(jobcode) = 'CI'; PAY_ ---1111 5555 NAME ----------Linda Costa Richard Jones JO -CI CI STARTDATE SALARY BONUS ---------15 -JAN-97 45000 1000 30 -OCT-92 50000 2000 Let’s say you have a big table and you don’t know whether some records have jobcode in upper case, some in lower case and some in mixed case. You want to find all CI jobcodes. The safest way to do this is to convert all of the jobcodes on the file to upper case for comparison purposes and then compare them to uppercase CI. This will guarantee you the best chance of finding all of the CI, ci, CI, c. I job codes.

For this example, I created a table made up of all character columns/fields. Character functions SQL> DESC char_table; Name Null? ----------------IDNO NAME CITY STATE Type ---CHAR(4) CHAR(20) CHAR(15) CHAR(2) SQL> SELECT * 2 FROM char_table; IDNO ---11 222 3333 NAME ----------John Doe Susan Ash Linda Forest CITY -------Seekonk Taunton Providence ST -MA MA RI I then used the concatenate symbol - || - to show the name concatenated with the city concatenated with the state as one string. Notice the blanks because CHAR fields pad with spaces to make the full length. If this had been done with VARCHAR 2 fields, you would see the fields together without the space padding. SQL> SELECT name || city || state 2 FROM char_table; Remember, each of these rows is one string field. NAME||CITY||STATE ------------------John Doe Seekonk MA Susan Ash Taunton MA Linda Forest Providence RI

Character functions SQL> SELECT RTRIM(name) || RTRIM(city) || state 2 FROM char_table; RTRIM(NAME)||RTRIM(CITY)||STATE ------------------John Doe. Seekonk. MA Susan Ash. Taunton. MA Linda Forest. Providence. RI By doing a RTRIM on name and city, I have eliminated the extra spaces and I see only the data. This is now strong together as one field containing just the data. A more readable example with comma and space concatenated in to the string is shown below. SQL> SELECT RTRIM(name) || ', ' || RTRIM(city) || ', ' || state 2 FROM char_table; RTRIM(NAME)||', '||RTRIM(CITY)||', '||STATE --------------------John Doe, Seekonk, MA Susan Ash, Taunton, MA Linda Forest, Providence, RI

Character functions SQL> SELECT ' 2 FROM DUAL; 'CIS 50' LTRIM ------CIS 50', LTRIM(' CIS 50') The LTRIM function trims spaces from the left.

Character functions SQL> SELECT RPAD(name, 20, '-'), LPAD(salary, 9, '*'), LPAD(bonus, 5, '$') 2 FROM first_pay; RPAD(NAME, 20, '-') ----------Linda Costa----John Davidson------Susan Ash-----Stephen York-------Richard Jones------Joanne Brown---- LPAD(SALA ----****45000 ****40000 ****25000 ****42000 ****50000 ****48000 LPAD( ----$1000 $1500 $$500 $2000 The RPAD function pads to the right and the LPAD function pads to the left. In this example, name is right padded to its length of 20 characters with the -. Salary is left padded with * to its length of 9 and bonus is left padded with $ to its length of 5. This kind of padding can be especially important with numeric fields that you do not want altered.

Character functions SQL> SELECT SUBSTR(datefst, 4, 3), datefst 2 FROM donor; SUB --JUL MAY JAN MAR APR DATEFST ----03 -JUL-98 24 -MAY-97 03 -JAN-98 04 -MAR-92 04 -APR-98 name of column/field position of first character of substring SUBSTR can be used to extract certain characters of data from a data string. In this case, I am extracting the month. The month starts in position 4 and goes for 3 characters. Therefore I use SUBSTR(datefst, 4, 3). length of substring NOTE: If length is not specified, you will get everything from the start point on. SQL> SELECT SUBSTR(datefst, 4) 2 FROM donor; SUBSTR(DATEFST, 4) --------------JUL-98 MAY-97 JAN-98 MAR-92 APR-98

Character functions SQL> SELECT * FROM donor; IDNO ----11111 12121 22222 23456 33333 34567 NAME -------Stephen Daniels Jennifer Ames Carl Hersey Susan Ash Nancy Taylor Robert Brooks STADR -------123 Elm St 24 Benefit St 21 Main St 26 Oak St 36 Pine St CITY -----Seekonk Providence Fall River ST -MA RI RI MA MA MA ZIP ----02345 02045 02720 DATEFST YRGOAL CONTACT ---------03 -JUL-98 500 John Smith 24 -M AY-97 400 Susan Jones 03 -J AN-98 Susan Jones 04 -M AR-92 100 Amy Costa 04 -M AR-92 50 John Adams 04 - APR-98 50 Amy Costa 6 rows selected. SQL> SELECT datefst, INSTR(datefst, 'A') 2 FROM donor; column/field being examined DATEFST INSTR(DATEFST, 'A') -------------03 -JUL-98 0 24 -MAY-97 5 03 -JAN-98 5 04 -MAR-92 5 04 -APR-98 4 6 rows selected. character being looked for No A in JUL A in 5 th character position in MAY A in 5 th character position in JAN A in 5 th character position in MAR A in 4 th character position in APR

Character functions SQL> SELECT name, LENGTH(name), stadr, LENGTH(stadr), city, LENGTH(city) 2 FROM donor; NAME LENGTH(NAME) STADR LENGTH(STADR) CITY LENGTH(CITY) --------------- -----------Stephen Daniels 15 123 Elm St 10 Seekonk 7 Jennifer Ames 13 24 Benefit St 13 Providence 10 Carl Hersey 11 24 Benefit St 13 Providence 10 Susan Ash 9 21 Main St 10 Fall River 10 Nancy Taylor 12 26 Oak St 9 Fall River 10 Robert Brooks 13 36 Pine St 10 Fall River 10 LENGTH tells the length of the characters entered into the column/field. NOTE: Embedded spaces are counted.

Character functions SQL> desc char_table; Name Null? ----------------IDNO NAME CITY STATE Type ---CHAR(4) CHAR(20) CHAR(15) CHAR(2) SQL> SELECT idno, LENGTH(idno), name, LENGTH(name), city, LENGTH(city), state, LENGTH(state) 2 FROM char_table; IDNO LENGTH(IDNO) NAME LENGTH(NAME) CITY LENGTH(CITY) ST LENGTH(STATE) ------------ -------- -- ------11 4 John Doe 20 Seekonk 15 MA 2 222 4 Susan Ash 20 Taunton 15 MA 2 3333 4 Linda Forest 20 Providence 15 RI 2 This table has all CHAR data. This means the data is padded with spaces. Notice that the length field is the length given when the table was created and not the length of the data stored in the field.

Character functions SQL> desc first_pay; Name Null? ----------------PAY_ID NAME JOBCODE STARTDATE SALARY BONUS Type ---VARCHAR 2(4) VARCHAR 2(20) CHAR(2) DATE NUMBER(9, 2) NUMBER(5) SQL> SELECT salary, LENGTH(salary), bonus, LENGTH(bonus) 2 FROM first_pay; SALARY LENGTH(SALARY) BONUS LENGTH(BONUS) --------------45000 5 1000 4 40000 5 1500 4 25000 5 500 3 42000 5 2000 4 50000 5 2000 4 48000 5 2000 4

Character functions SQL> SELECT jobcode, REPLACE(jobcode, 'CI', 'IT') 2 FROM first_pay; JO -CI IN AP CM CI IN REPL ---IT IN AP CM IT IN In this example, all rows/records that contain CI as the jobcode are displayed with IT as the jobcode.

Character functions SQL> SELECT SUBSTR(startdate, 4, 3) || ' ' || SUBSTR(startdate, 1, 2) || ', ' || SUBSTR(startdate, 8, 2) 2 FROM first_pay; SUBSTR(STA -----JAN 15, 97 SEP 25, 92 FEB 05, 00 JUL 03, 97 OCT 30, 92 AUG 18, 94 This code extracts the month for the date, concatenates it with a space, then extracts the day from the date, concatenates it with a comma space and extracts the year from the date.

Character function SUBSTR(UPPER(name), 1, 2) SUBSTR(idno, 4, 2) First UPPER converts the name to upper case. Then SUBSTR takes the upper case name starts at character 1 and extracts 2 characters. The characters are therefore the first two characters of the name. This code will start with the fourth character of the column/field idno and extract two characters. In other words, it will extract the fourth and fifth characters. SQL> SELECT SUBSTR(UPPER(name), 1, 2) || SUBSTR(stadr, 1, INSTR(stadr, ' ')-1) || SUBSTR(idno, 4, 2) 2 FROM donor; SUBSTR(UPPER(NAME), ---------ST 12311 JE 2421 CA 2422 SU 2156 NA 2633 RO 3667 6 rows selected. SUBSTR(stadr, 1, INSTR(stadr, ' ')-1) This code will extract a substring from stadr. It will start with the first character. The number of characters taken will be determined by using INSTR to find the space in the street address and then subtract 1 from it. Essentially this gives you the street number. Note that INSTR is determine before SUBSTR.