1 / 9

Understanding SQL Functions and Date Formatting in Oracle

This guide provides an overview of essential SQL functions in Oracle, focusing on character functions, the use of the SELECT statement with WHERE clauses, and how to format dates effectively. It covers operators like '=', '<', and the use of 'IN', 'BETWEEN', and 'LIKE' for filtering data. Additionally, it explains how to extract last names from full names using nesting functions and highlights the importance of datatype conversions. Examples in the context of customer and employee tables illustrate these principles in action.

bishop
Télécharger la présentation

Understanding SQL Functions and Date Formatting in Oracle

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL – FunctionsCharacter Functions &TO_CHAR Date fromatting 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

More Related