SQL Lesson4 Chapter 4 Functions

1
SQLLesson4Chapter 4Functions
2

• Date Calculations Formats
• Jon, Came up with these great websites
• http//www.mysql.com/doc/en/Date_calculations.htm
  l
• http//www.mysql.com/doc/en/Date_and_time_functio
  ns.html
• http//www.mysql.com/doc/en/Date_and_time_types.h
  tml
• Select CURDATE()
• SELECT DATE_FORMAT(CURDATE(),M/D/Y)

3

• Date and Time MYSQL YYYY-MM-DD
• DAYOFWEEK(date)/DAYOFMONTH(date)/DAYOFYEAR(date)
• Returns the number of the day,month and year
• Select DAYOFWEEK(2002-09-26)
• Where 1 Sunday, 2Monday
• 5
• DAYNAME(date)/MONTHNAME(date)
• Returns the name
• Select DAYNAME(2002-09-26)
• Thursday
• CURDATE()/CURRENT_DATE/CURTIME()CURRENT_TIME
• - Returns the Current Date or Time

4

• SQL Functions in the SELECT clause
• SELECT po_number, TO_CHAR(order_dateMM)
• FROM po_number (doesnt work)
• SELECT po_number, MONTHNAME(order_date)
• FROM po_header
• SELECT vendor_id, po_number
• FROM po_header
• WHERE DAYOFMONTH(order_date) 06
• SELECT lastname,firstname
• FROM person
• WHERE UPPER (SUBSTR(lastname,1,2)) MC
• (starting position, number of
  characters)
• MYSQL Change SUBSTR to SUBSTRING

5

• SQL String Functions
• SUBSTRING - starting position, number of
  characters
• WHERE substr(phone_number,1,3) 744
• RTRIM()/LTRIM()/TRIM() - trim leading, trailing
  blanks, or both from a string
• WHERE rtrim(zip) 80111.
• UCASE()/LCASE()
• UPPER()/LOWER - converts all characters in the
  string
• SELECT UPPER(lastname)
• FROM person

6

• String Functions
• INSTR - find the position in the string
• WHERE INSTR(description, SCSI) gt 0
• SOUNDEX - does a string look like or (sound
  like) another string
• WHERE soundex(lastname) soundex(john
  son)
• CONCAT - combining, same as in ACCESS
• SELECT CONCAT(lastname , , ,firstname)

7

• SQL Numeric Functions
• ABS(n) - absolute value
• WHERE Abs(Cost) gt 100
• CEILING(n) - smallest integer
• SELECT Ceil(Age)
• FLOOR(n) - largest integer
• SELECT Floor(Age)
• ROUND(n,m) - round field, to the nth
• SELECT Round((Cost1.20),2)
• TRUNCATE(n,m) - truncate field to the nth
• SELECT Trunc(Inv_Desc,25)