SQL – Functions Character Functions & TO_CHAR Date formatting

1. SQL – FunctionsCharacter Functions &TO_CHAR Date formatting CIS 310

2. Basic SELECT Statement WHERE Clause Operators • =, <, >, <=, >= • IN(List) WHERE CODE IN (‘ABC’, ‘DEF’, ‘HIJ’) - would return only rows with one of those 3 literal values for the code attribute • BETWEEN min_val AND max_val WHERE Qty_Ord BETWEEN 5 and 15 - would return rows where Qty_Ord is >= 5 and <= 15 - Works on character data using ascending alphabetical order • LIKE “literal with wildcards” % used for multiple chars. _ single char. WHERE Name LIKE ‘_o%son’ - returns rows where name has o as the 2nd character and ends with son - Torgeson or Johnson • NOT WHERE NOT Name = ‘Johnson’ - would return all rows where name is not Johnson - lowest priority in operator order • AND and OR, Use Parentheses to control order

3. Commonly Used Single Row Character Functionsin ORACLEThese functions return character values

4. Example of Character Data Single Row Functions Select SUBSTR(f_name,1,1) || '. ' || UPPER(l_name) as NAME, INITCAP(State) || '.' as ST, LENGTH(str_address) as adr_length from CUSTOMER; NAME ST ADR_LENGTH ------------- --- ---------- M. JORDAN Az. 10 A. ALDA Az. 11 D. DAVIS Az. 14 E. BARNES Az. 13 S. DAVIS Az. 15 A. EVANS Ca. 11

5. Nesting Character Functions EMP_ID E_NAME HIRE_DATE --------- --------------- ----------- 57 Jan Jones 02-JAN-99 28 Sam Mann 14-MAY-07 84 Al Cowl 04-OCT-11 17 Ann Davis 12-JAN-08 • Given the EMPLOYEE table shown, to retrieve an employee’s last name, find the blank character in E_Name – INSTR(E_Name, ‘ ‘) • Last name must begin 1 character after the blank - SUBSTR(E_Name, INSTR(E_NAME,’ ‘)+1) • E.G. SELECT Substr(E_Name, Instr(E_NAME,' ')+1) FROM EMPLOYEE; Produces Jones Mann Cowl Davis

6. Datatype Conversion Functions in ORACLE • Many datatypes are automatically (implicitly) converted when differing but compatible datatypes are used in an expression. • Explicit conversion is required primarily for conversion and display of date/time data to character form and conversion of character strings to date/time values. • There are 3 explicit conversion functions

7. Date Formatting • Dates are stored as a consolidated date/time value • They are internally stored as numeric values that increase by 1 for each day, and are fractionally incremented to represent the time of day. • E.g. if midnight today is 533,278, 8 AM is 533,278.333 and noon is 533,278.5 • By default dates are displayed as DD-MON-YY • E.g. 12-DEC-12 • Dates entered as character values formatted either as ‘DD-MON-YYYY’ or ‘DD-MON-YY’ are implicitly converted to dates for storage • E.g. ’12-DEC-2012’ or ‘12-DEC-12’

8. 0 Commonly Used Date Format Elements • Oracle provides a large number of date and numeric format elements which can be used in the fmt portion of the conversion functions. • Only a small subset of the most frequently used date format elements are presented here.

9. Altering the Format of Dates • For dates, the form of the to_char function is TO_CHAR(column_name , 'date/time formatting characters'). • For example: select ord_no, to_char(ord_date, 'mm-dd-yy') as date1, to_char(ord_date, 'Mon-yyyy') as date2 from sale; • Produces output like: ORD_NO DATE1 DATE2 ---------- -------- -------- 2901 10-23-10 Oct-2010 2902 10-23-10 Oct-2010 2913 10-24-10 Oct-2010