Previous Page
Next Page

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).

Tip

Always Use Four-Digit Years Two-digit years are supported, and MySQL treats years 00-69 as 2000-2069 and 70-99 as 1970-1999. While these might in fact be the intended years, it is far safer to always use a full four-digit year so MySQL does not have to make any assumptions for you.


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';

Tip

If You Mean Date Use Date() It's a good practice to use Date() if what you want is just the date, even if you know that the column only contains dates. This way, if somehow a date time value ends up in the table in the future, your SQL won't break. Oh, and yes, there is a Time() function, too, and it should be used when you want the time.

Both Date() and Time() were first introduced in MySQL 4.1.1.


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.

Note

MySQL Version Differences Many of the MySQL date and time functions were added in MySQL 4.1.1. If you are using an earlier version of MySQL, be sure to consult your documentation to determine exactly which of these functions is available to you.


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



Previous Page
Next Page