Using Functions

Most SQL implementations support the following types of functions:

• Text functions are used to manipulate strings of text (for example, trimming or padding values and converting values to upper- and lowercase).

• Numeric functions are used to perform mathematical operations on numeric data (for example, returning absolute numbers and performing algebraic calculations).

• Date and time functions are used to manipulate date and time values and to extract specific components from these values (for example, returning differences between dates and checking date validity).

• System functions return information specific to the DBMS being used (for example, returning user login information or checking version specifics).

Text Manipulation Functions

You've already seen an example of text-manipulation functions in the last chapterthe RTrim() function was used to trim white space from the end of a column value. Here is another example, this time using the Upper() function:

• Input

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

• Output

+----------------+------------------+
| vend_name      | vend_name_upcase |
+----------------+------------------+
| ACME           | ACME             |
| Anvils R Us    | ANVILS R US      |
| Furball Inc.   | FURBALL INC.     |
| Jet Set        | JET SET          |
| Jouets Et Ours | JOUETS ET OURS   |
| LT Supplies    | LT SUPPLIES      |
+----------------+------------------+

• Analysis

As you can see, Upper() converts text to uppercase and so in this example each vendor is listed twice, first exactly as stored in the vendors table, and then converted to uppercase as column vend_name_upcase.

Table 11.1 lists some commonly used text-manipulation functions.

Table 11.1. Commonly Used Text-Manipulation Functions

Function	Description
Left()	Returns characters from left of string
Length()	Returns the length of a string
Locate()	Finds a substring within a string
Lower()	Converts string to lowercase
LTrim()	Trims white space from left of string
Right()	Returns characters from right of string
RTrim()	Trims white space from right of string
Soundex()	Returns a string's SOUNDEX value
SubString()	Returns characters from within a string
Upper()	Converts string to uppercase

One item in Table 11.1 requires further explanation. SOUNDEX is an algorithm that converts any string of text into an alphanumeric pattern describing the phonetic representation of that text. SOUNDEX takes into account similar sounding characters and syllables, enabling strings to be compared by how they sound rather than how they have been typed. Although SOUNDEX is not a SQL concept, MySQL (like many other DBMSs) offers SOUNDEX support.

Here's an example using the Soundex() function. Customer Coyote Inc. is in the customers table and has a contact named Y. Lee. But what if that were a typo, and the contact actually was supposed to have been Y. Lie? Obviously, searching by the correct contact name would return no data, as shown here:

• Input

SELECT cust_name, cust_contact
FROM customers
WHERE cust_contact = 'Y. Lie';

• Output

+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+

Now try the same search using the Soundex() function to match all contact names that sound similar to Y. Lie:

• Input

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y Lie');

• Output

+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+

• Analysis

In this example, the WHERE clause uses the Soundex() function to convert both the cust_contact column value and the search string to their SOUNDEX values. Because Y. Lee and Y. Lie sound alike, their SOUNDEX values match, and so the WHERE clause correctly filtered the desired data.

Date and Time Manipulation Functions

Date and times are stored in tables using special datatypes using special internal formats so they may be sorted or filtered quickly and efficiently, as well as to save physical storage space.

The format used to store dates and times is usually of no use to your applications, and so date and time functions are almost always used to read, expand, and manipulate these values. Because of this, date and time manipulation functions are some of the most important functions in the MySQL language.

Table 11.2 lists some commonly used date and time manipulation functions.

Table 11.2. Commonly Used Date and Time Manipulation Functions

Function	Description
AddDate()	Add to a date (days, weeks, and so on)
AddTime()	Add to a time (hours, minutes, and so on)
CurDate()	Returns the current date
CurTime()	Returns the current time
Date()	Returns the date portion of a date time
DateDiff()	Calculates the difference between two dates
Date_Add()	Highly flexible date arithmetic function
Date_Format()	Returns a formatted date or time string
Day()	Returns the day portion of a date
DayOfWeek()	Returns the day of week for a date
Hour()	Returns the hour portion of a time
Minute()	Returns the minute portion of a time
Month()	Returns the month portion of a date
Now()	Returns the current date and time
Second()	Returns the second portion of a time
Time()	Returns the time portion of a date time
Year()	Returns the year portion of a date

This would be a good time to revisit data filtering using WHERE. Thus far we have filtered data using WHERE clauses that compared numbers and text, but frequently data needs to be filtered by date. Filtering by date requires some extra care, and the use of special MySQL functions.

The first thing to keep in mind is the date format used by MySQL. Whenever you specify a date, be it inserting or updating table values, or filtering using WHERE clauses, the date must be in the format yyyy-mm-dd. So, for September 1st, 2005 specify 2005-09-01. Although other date formats might be recognized, this is the preferred date format because it eliminates ambiguity (after all, is 04/05/06 May 4th 2006, or April 5th 2006, or May 6th 2004, or... you get the idea).

As such, a basic date comparison should be simple enough:

• Input

SELECT cust_id, order_num
FROM orders
WHERE order_date = '2005-09-01';

• Output

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+

• Analysis

That SELECT statement worked; it retrieved a single order record, one with an order_date of 2005-09-01.

But is using WHERE order_date = '2005-09-01' safe? order_date has a datatype of datetime. This type stores dates along with time values. The values in our example tables all have times of 00:00:00, but that might not always be the case. What if order dates were stored using the current date and time (so you'd not only know the order date but also the time of day that the order was placed)? Then WHERE order_date = '2005-09-01' fails if, for example, the stored order_date value is 2005-09-01 11:30:05. Even though a row with that date is present, it is not retrieved because the WHERE match failed.

The solution is to instruct MySQL to only compare the specified date to the date portion of the column instead of using the entire column value. To do this you must use the Date() function. Date(order_date) instructs MySQL to extract just the date part of the column, and so a safer SELECT statement is

• Input

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01';

Now that you know how to use dates to test for equality, using all of the other operators (introduced in Chapter 6, "Filtering Data") should be self-explanatory.

But one other type of date comparison warrants explanation. What if you wanted to retrieve all orders placed in September 2005? A simple equality test does not work as it matches the day of month, too. There are several solutions, one of which follows:

• Input

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

• Output

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+

• Analysis

Here a BETWEEN operator is used to define 2005-09-01 and 2005-09-30 as the range of dates to match.

Here's another solution (one that won't require you to remember how many days are in each month, or worry about Feburary in leap years):

• Input

SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

• Analysis

Year() is a function that returns the year from a date (or a date time). Similarly, Month() returns the month from a date. WHERE Year(order_date) = 2005 AND Month(order_date) = 9 thus retrieves all rows that have an order_date in year 2005 and in month 9.

Numeric Manipulation Functions

Numeric manipulation functions do just thatmanipulate numeric data. These functions tend to be used primarily for algebraic, trigonometric, or geometric calculations and, therefore, are not as frequently used as string or date and time manipulation functions.

The ironic thing is that of all the functions found in the major DBMSs, the numeric functions are the ones that are most uniform and consistent. Table 11.3 lists some of the more commonly used numeric manipulation functions.

Table 11.3. Commonly Used Numeric Manipulation Functions

Function	Description
Abs()	Returns a number's absolute value
Cos()	Returns the trigonometric cosine of a specified angle
Exp()	Returns the exponential value of a specific number
Mod()	Returns the remainder of a division operation
Pi()	Returns the value of pi
Rand()	Returns a random number
Sin()	Returns the trigonometric sine of a specified angle
Sqrt()	Returns the square root of a specified number
Tan()	Returns the trigonometric tangent of a specified angle