Functions and string manipulation

Lab 2 Week 2

Date functions• Commonly used date functions are:

– sysdate – next_day– add_months– last_day– months_between– least– greatest– round– trunc

Date functions and the DUAL table.

• Current date and time:SQL> select sysdate from dual;

SYSDATE---------30-SEP-05

• Dual:– This supplies values for system

variables. Current date and time is one of them. We will come across more later.

Sample date functionsSQL> select 2 order_date as "Date", 3 to_char(order_date,'DAY'), 4 next_day(order_date,'MONDAY') as "Monday following", 5 last_day(order_date) as "Last day of month", 6 add_months (order_date,3) as "3 months later" 7 from corder;

Meaning…• To_char(date,format)

– Converts the date to the specified format.

• Next_day(date,dayofweek)– Gives the date of the next ‘dayofweek’ after the date given.

• Last_day(date)– Gives the last day of the month in the date specified.

• add_months (date,int)– Adds int months to the given date.

Produces…Date TO_CHAR(O Monday fo Last day 3 months--------- --------- --------- --------- ---------02-FEB-02 SATURDAY 04-FEB-02 28-FEB-02 02-MAY-0204-FEB-05 FRIDAY 07-FEB-05 28-FEB-05 04-MAY-0504-FEB-05 FRIDAY 07-FEB-05 28-FEB-05 04-MAY-0506-FEB-05 SUNDAY 07-FEB-05 28-FEB-05 06-MAY-0506-FEB-05 SUNDAY 07-FEB-05 28-FEB-05 06-MAY-0510-FEB-05 THURSDAY 14-FEB-05 28-FEB-05 10-MAY-0512-FEB-05 SATURDAY 14-FEB-05 28-FEB-05 12-MAY-0518-FEB-05 FRIDAY 21-FEB-05 28-FEB-05 18-MAY-0522-FEB-05 TUESDAY 28-FEB-05 28-FEB-05 22-MAY-0512-FEB-05 SATURDAY 14-FEB-05 28-FEB-05 12-MAY-05

10 rows selected.

Date functions

• Sysdate gives current date• Next_day(d,day) where d is a date and day is a string

representing a day of the week. – E.g. next_day(’14-dec-2005’,’Monday’) will return ’19-dec-2005’

• Add_months(d,count) adds n months to d.• Last_day(d) returns the date corresponding to the last

day of the month in which d belongs.• Months_between(d1,d2)• Least(d1,d2,…,dn)• Greatest(d1,…,dn)• Trunc(d) returns the date (d) with the time at midnight.

Functions in SQL

• There are many types of functions provided. • The ones that are used most are:

– Date and Time functions– Mathematical functions– String functions

• There follows a list of all functions in these categories.

• We will practice only the most popularly used.

All about dates

• Dates are relative – i.e. the date and time are the same function.

• The current date and time depends on where you are in the world.

• The date format '12-dec-2005' will work, but NOT ’12-dec-2005’. – The apostrophes, or quotes, are different.

• Microsoft Word / PowerPoint will automatically change from ' to ‘.

Formatting the date• To_char(d,format) returns the date in the format specified:

– Mm returns month number– Mon returns the month in 3-character format– D returns the day number in the week– DD returns the day number in the month– DDD returns the day number in the year– DY gives the weekday in 3-character format– DAY gives the weekday name– Y returns the last digit of the year– Yy returns the last 2 digits of the year– Yyyy returns the 4-digit year– Hh12 returns the hours of the day(1 -12)– Hh24 returns the hours of the day (1 – 24)– Mi returns the minutes of the hour– Ss returns the seconds of the minute– AM returns AM or PM

resources

• These sites are helpful:– http://www.techonthenet.com/oracle/index.php– http://www.ss64.com/orasyntax/

To put a name on a column• Use the ‘as’ clause to give a name

to a column.– Unitprice as Price or– UnitPrice as “Unit Price”– Note double quotes.

• This can be used on any column, but is especially useful in a derived column.

• New columns can be derived from existing fields:

• E.g. the value of an item in stock is the number in stock by the unit price.

• Surround the alias with double quotes:

SQL> select stock_description as "Name" from stock;

Name--------------------Brick - red, 30x100Cavity blocks(100)2"x4" lengths6" Nails(50)6" Nails(100)Workbenchcordless DrillCavity blocks(500)Cavity blocks(200)9 rows selected.SQL>

Naming sampleSQL> select 2 stock_description as "Name", 3 QuantityRequired as "Quantity", 4 Unit_Price as "at Price", 5 Unit_Price * QuantityRequired as "SubTotal" 6 from Stock join Corderline on 7 stock.stock_code = corderline.stock_code;

Name Quantity at Price SubTotal---------------- ---------- ---------- ----------Brick - red, 30x100 200 2.5 500

String functions - Concatenation• Concatenation:

SQL> select supplier_name||','||supplier_address from supplier;SUPPLIER_NAME||','||SUPPLIER_ADDRESS------------------------------------------------------------------

--------------Buckleys,Quarry town, Quarrysville, D44.Brendan Moore,44 Kevin St., D8James McGovern,33 Synge St.Liam Keenan,33 Mount Vernon AveMary O'Brien,Appian Way, D2Oliver Moore,Georges St., D2June Browne,33 Liberty LanePaul Sloan,44 Liberty LaneKevin Kelly,33 Bride St, D8Robert O'Mahony,Fitzwilliam SqPatricia O'Brien,21 Liberty Lane, D8

11 rows selected.

String manipulation

• Concatenation: use || instead of ,

• Pad out a string (from the left) to a specified length:– Lpad(string,length,’padding char’)– Rpad does the same, but pads from the right.

• Trim strings of characters uses– Ltrim(string,’trim char’)– Rtrim trims from the right.

Example of lpadSQL> select lpad(stock_code,12,'x') as "stock code",

stock_description from stock;

stock code STOCK_DESCRIPTION------------ --------------------xxxxxxxxA111 Red bricks(100)xxxxxxxBRK11 Brick - red, 30x100xxxxxxxxA101 Cavity blocks(100)xxxxxxxxB101 2"x4" lengthsxxxxxxxxB111 Window Frames 2'x4'xxxxxxxxC101 6" Nails(50)xxxxxxxxC121 6" Nails(100)xxxxxxxxD101 WorkbenchxxxxxxxxD131 cordless DrillxxxxxxxxE101 Cavity blocks(500)xxxxxxxxE141 Cavity blocks(200)

11 rows selected.

Trim from the rightSQL> select stock_code, rtrim(stock_code,'1') from

stock;STOCK RTRIM----- -----A101 A10A111 AB101 B10B111 BBRK11 BRKC101 C10C121 C12D101 D10D131 D13E101 E10E141 E1411 rows selected.

Look up…

• Lower(string)

• Upper(string)

• Length(string)

• Substr(string,start,[n])

• Instr(string,’chars’[,start [,n]])

Exercises

• Retrieve the system date and display it in 5 different formats.

• To demonstrate the effectiveness of ‘order by’:– Select all from the builder’s sorderline table– Select all from the builder’s sorderline table in

order of the quantity of stock that is required.– Select all from the builder’s sorderline table in

descending order of the quantity of stock that is required.

More exercises

• Show the order id, the customer id and the day, month and year of the order date, for all orders, in customer id order.

• Display the supplierOrderDate, the DeliveredDate and the difference in days between the two, for all orders that have been shipped (i.e. deliveredDate is not null)

More exercises

• Display the order id, the required date, the shipped date where the shipped date is after the required date, the number of days by which it is late, ordered by the number of days by which it is late.

• Display the orderdate and two weeks after the orderdate for each order that has not been shipped (shippeddate is null).

Exercises

• Amend the previous exercises to put names on the columns.

• Format your outputs so that they are easy and pleasant to read.

• Try out the ‘where’ clause on the sample database, changing the conditions.

Achievements

• Writing Basic SQL Select Statements– [√]List the capabilities of SQL SELECT statements– [√]Execute a basic SELECT statement

• Restricting and Sorting Data– [√]Limit the rows retrieved by a query – [√] Sort the rows retrieved by a query

• Single-Row Functions – [√] Describe various types of functions available in

SQL – [√] Use character, number, and date functions in

SELECT statements– [√] Use conversion functions