SQL – Functions Character Functions & TO_CHAR Date & Number Formatting

1. SQL – FunctionsCharacter Functions &TO_CHAR Date & Number 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 WKR_ID WKR_NAME HIRE_DATE ------ -------------------- ------------------------- 1234 Stan Mann 14-MAY-04 2345 Jan Grand 14-SEP-06 3456 Dean Green 24-FEB-98 • Jake Brock 05-OCT-09 . . . Given the WORKER table shown, to retrieve a worker’s last name, we must find the blank character in Wkr_Name –> INSTR(Wkr_Name, ‘ ‘) • Last name must begin 1 character after the blank - SUBSTR(Wkr_Name, INSTR(Wkr_NAME,’ ‘)+1) • E.G. SELECT Substr(Wkr_Name, Instr(Wkr_NAME,' ')+1) FROM WORKER; Produces Mann Grand Green Brock . . .

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

10. TO_CHAR Formatting of Numbers TO_CHAR(number, ‘formatting chars.’) • , and . – literals • $ literal prints just in front of first printed numeric digit. • 9s – Replaced by numeric value, blank if leading 0 • 0s – Replaced by numeric values 0 printed if leading 0

11. Examples of TO_CHAR for numbers • To_char(18.75, ‘$990.00’) -> $18.75 • To_char(18.75, ‘$000.00) -> $018.75 • To_char(1275.5, ‘99,990.00’) -> 1,276.50

12. An Expression as a Column • Any mathematical operation that returns a single value can be treated as a column in a query. For example from the APEX PRODUCT table we might want a discounted price of markup percentage. The query below would return those values: SELECT Prod_Code, Standard_Price *.9 As Discounted_Price, Standard_Price/Unit_Cost as Markup_Pct FROM PRODUCT;