8 How Do We Use Stored Programs (Functions)?
Functions
In MySQL, a function is a stored program, or operation that when performed on data will returns a value or manipulate or covert the data. This allows us to perform manipulations on the data before retrieving or saving it. Functions have a name that you refer to and can also accept one or more parameters that can be passed into the function.
(parameter1, parameter2, ...)
In this lesson, we will be looking at scalar functions that operate on the individual rows of a table. Scalar means the function will work on a single entity of data, not a group of data together. So if the function is used with a table with many rows the function will work on each single value or entity of data in each row.
Types of Functions:
| Text |
used to manipulate strings of text |
| Numeric |
used to perform mathematical operations on numeric data |
| Date & Time |
used to manipulate data and time values and to extract specific components |
| System |
returns information specific to the DBMS being used |
We will be going over some of the more common text, numeric, and date & time functions.
Commonly used Text-manipulation or String Functions
| Function
| Description
|
| CONCAT(string, string) |
Returns all the string combined into one string |
| LEFT(string, length) |
Returns characters from the left of the string |
| RIGHT(string, length) |
Returns characters from the right of the string |
| LTRIM(string) |
Trims white space from the left of the string |
| RTRIM(string) |
Trims white space from the right of the string |
| TRIM( string) |
Trims white space from the left and right of the string |
| LENGTH(string) |
Returns the length of characters of the string |
| LOWER(string) |
Converts the string to lowercase |
| UPPER(string) |
Converts the string to uppercase |
| LOCATE(find, search, start) |
Find a substring within the string |
| SUBSTRING(string, start, length ) |
Return characters from within a string |
Let’s use the keyword table to demonstrate some of the functions.
SELECT * FROM keyword
| keyword_id |
keyword |
| 1 |
sky |
| 2 |
landscape |
| 3 |
people |
| 4 |
nature |
SELECT CONCAT(‘The primary key of ‘, keyword, ‘ is ‘, keyword_id) AS new_string,
FROM keyword
WHERE keyword_id = 1
| new_string |
| The primary key of sky is 1 |
The result is made up of string literals inside quotes and column values all combined into one new string.
SELECT LEFT(keyword, 4), LENGTH(keyword), UPPER(keyword)
FROM key
WHERE keyword_id = 2
| LEFT(keyword, 4) |
LENGTH(keyword) |
UPPER(keyword) |
| land |
9 |
LANDSCAPE |
The first column takes the string ‘landscape’ and returns 4 characters from the left.
The second column adds up all the characters in the string ‘landscape’
The third column converts all the letters in the string ‘landscape’ to upper case.
Index numbering of the characters in the string ‘landscape’
| l |
a |
n |
d |
s |
c |
a |
p |
e |
| 1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
SELECT LOCATE( ‘and’, keyword), SUBSTRING(keyword, 1, 5), SUBSTRING(keyword, 7)
FROM key
WHERE keyword_id = 2
| LOCATE(‘and’, keyword) |
SUBSTRING(keyword, 1, 5) |
SUBSTRING(keyword, 7) |
| 2 |
lands |
ape |
The first column searches for the substring ‘and’ inside of the string ‘landscape’ and returns the index number of where it was found. the 2nd character in is where the ‘a’ in ‘and’ starts.
The second column starts at the first character and returns all character from there to the fifth character.
The third column starts at the seventh character and returns the rest of the string from there.
Commonly used Numeric Functions
| Function
| Description
|
| ROUND(number, length) |
Returns the number rounded |
| FLOOR(number) |
Returns the next smaller whole number |
| CEILING(number) |
Returns the next larger whole number |
| ABS(number) |
Returns the absolute value of the number |
| SQRT(number) |
Returns the square root of the number |
| MOD(number, divisor) |
Returns the remainder of the number divided by the divisor |
| FORMAT(number, decimals, locale) |
Returns the number is currency format |
Lets use the magazine table to demonstrate some of the numeric functions
SELECT * FROM magazine
| magKey |
magName |
magPrice |
| 1 |
Fishing in the Mojave |
13.95 |
| 2 |
Car Racing Made Easy |
15.45 |
| 3 |
Pine Cone Computing |
17.50 |
| 4 |
Cooking Like Mad |
18.00 |
| 5 |
If Only I Could Sing |
25.00 |
SELECT ROUND(magPrice), ROUND(magPrice, 1)
FROM magazine
| ROUND(magPrice) |
ROUND(magPrice,1) |
| 14 |
14.0 |
| 15 |
15.5 |
| 18 |
17.5 |
| 18 |
18.0 |
| 25 |
25.0 |
The first column rounds to the nearest whole number.
The second column rounds to one decimal place.
SELECT magPrice, FLOOR(magPrice), CEILING(magPrice)
FROM magazine
| magPrice |
FLOOR(magPrice) |
CEILING(magPrice) |
| 13.95 |
13 |
14 |
| 15.45 |
15 |
16 |
| 17.50 |
17 |
18 |
| 18.00 |
18 |
18 |
| 25.00 |
25 |
25 |
The number 13.95 has the next lower whole number of 13 and next larger whole number of 14.
The number 15.45 has the next lower whole number of 15 and the next larger whole number of 16.
The number 17.50 has the next lower whole number of 17 and the next larger whole number of 18.
The number 18.00 has the next lower whole number of 18 and the next larger whole number of 18.
The number 25.00 has the next lower whole number of 25 and the next larger whole number of 25.
SELECT SQRT(magPrice)
FROM magazine
WHERE magKey = 5
The square root of 25 is 5.
If you wanted to manipulate your magazine prices to look more like currency you can use the FORMAT function. The first parameter is the number, the second the decimal places and the third is the locale which determines the thousands separator. Ours will be ‘en_US’ to show US English formatting. 10,000 dollars will be added to each magazine price so we can see the commas placed at each thousands grouping. The CONCAT function will also be used to add the ‘$’ dollar sign to the number.
SELECT CONCAT(‘$’, FORMAT(magPrice + 10000, 2, ‘en_US’))
FROM magazine
| CONCAT(‘$’, FORMAT(magPrice + 10000, 2, ‘en_US’)) |
| $10,013.95 |
| $10,015.45 |
| $10,017.50 |
| $10,018.00 |
| $10,025.00 |
Commonly used Date and Time Functions
| Function
| Description
|
| YEAR(date) |
Returns the year portion of a date |
| MONTH(date) |
Returns the month portion of a date |
| DAY(date) |
Returns the day portion of a date |
| HOUR(time) |
Returns the hour portion of a time |
| MINUTE(time) |
Returns the minute portion of a time |
| SECOND(time) |
Returns the second portion of a time |
| NOW() |
Returns the current local time and date from your system |
| DATE_ADD(date, INTERVAL length time) |
Adds a specified interval to a date |
| DATEDIFF(date, date) |
Calculates the difference in days between two dates |
| DATE_FORMAT(date, format) |
Returns a string for a date with specific formatting |
Let’s use the employee table to demonstrate some of the date and time functions.
SELECT * FROM employee
| emp_id |
emp_fname |
emp_lname |
hire_date |
emp_email |
| 1 |
Jenny |
Jensen |
2013-06-15 |
jensenj@salon.com |
| 2 |
Haley |
Lopez |
2013-08-23 |
lopezh@salon.com |
| 3 |
Robert |
Green |
2014-01-03 |
greenr@salon.com |
| 4 |
Olive |
Adams |
2015-07-12 |
adamso@salon.com |
| 5 |
Julie |
Davis |
2015-10-20 |
davisj@salon.com |
SELECT hire_date, YEAR(hire_date), MONTH(hire_date)
FROM employee
| hire_date |
YEAR(hire_date) |
MONTH(hire_date) |
| 2013-06-15 |
2013 |
6 |
| 2013-08-23 |
2013 |
8 |
| 2014-01-03 |
2014 |
1 |
| 2015-07-12 |
2015 |
7 |
| 2015-10-20 |
2015 |
10 |
The YEAR function takes the hire date and extracts only the year and the MONTH function only extracts out the month from the date.
SELECT NOW()
| NOW() |
| 2020-12-28 13:26:17 |
The NOW function returns the systems current date. When you run this function it will be the current date and time of your system.
SELECT hire_date AS Hire Date, DATE_ADD(hire_date, INTERVAL 6 MONTH) AS 'Six Months Later'
FROM employee
| Hire Date |
Six Months Later |
| 2013-06-15 |
2013-12-15 |
| 2013-08-23 |
2014-02-23 |
| 2014-01-03 |
2014-07-03 |
| 2015-07-12 |
2016-01-12 |
| 2015-10-20 |
2016-04-20 |
SELECT DATEDIFF(hire_date, '2013-01-01') AS ‘Days Between’
FROM employee
| Days Between |
| 165 |
| 234 |
| 367 |
| 922 |
| 1022 |
This gives the difference in days between January 1st 2013 and the hire date. If you want years, you would need to divide it by 365.
SELECT DATEDIFF(hire_date, '2013-01-01') / 365 AS ‘Years Between’
FROM employee
| Years Between |
| 0.4521 |
| 0.641 |
| 1.0055 |
| 2.5260 |
| 2.8000 |
The DATE FORMAT function allows you to format the date so it isn’t always in the format of yyyy-mm-dd.
SELECT hire_date, DATE_FORMAT(hire_date, ‘%M %D, %Y’) AS ‘hire date formatted’
FROM employee
| hire_date |
hire date formatted |
| 2013-06-15 |
June 15th, 2013 |
| 2013-08-23 |
August 23rd, 2013 |
| 2014-01-03 |
January 3rd, 2014 |
| 2015-07-12 |
July 12th, 2015 |
| 2015-10-20 |
October 20th, 2015 |
The second parameter is a format string that includes codes for formatting the different parts of the date. Here are some of the common codes for formatting dates:
| Format Code |
Description |
| %c |
Month, numeric |
| %M |
Month name |
| %e |
Day of the month, numeric |
| %D |
Day of the month with suffix |
| %y |
Year, 2 digits |
| %Y |
Year, 4 digits |
| %W |
Weekday name |
There are also format codes for formatting times with the function TIME_FORMAT. But we won’t be using any of those in this course.
There are many more functions than we have not covered here, but for the depth of this course, these are the text, numeric, and date & time functions we will be focusing on.