USING DATA MANIPULATION FUNCTIONS

Functions Are Less Portable Than SQL Code that runs on multiple systems is said to be portable. Most SQL statements are relatively portable, and when differences between SQL implementations do occur they are usually not that difficult to deal with. Functions, on the other hand, tend to be far less portable. Just about every major Database Management System (DBMS) supports functions that others don't, and sometimes the differences are significant.

Understanding Functions

Like almost any other computer language, SQL supports the use of functions to manipulate data. Functions are operations that are usually performed on data, usually to facilitate conversion and manipulation.

An example of a function is the RTrim() that we used in the last tutorial to trim any spaces from the end of a string.

Note

Functions Are Less Portable Than SQL Code that runs on multiple systems is said to be portable. Most SQL statements are relatively portable, and when differences between SQL implementations do occur they are usually not that difficult to deal with. Functions, on the other hand, tend to be far less portable. Just about every major Database Management System (DBMS) supports functions that others don't, and sometimes the differences are significant.

With code portability in mind, many SQL programmers opt not to use any implementation-specific features. Although this is a somewhat noble and idealistic view, it is not always in the best interests of application performance. If you opt not to use these functions, you make your application code work harder. It must use other methods to do what the DBMS could have done more efficiently.

If you do decide to use functions, make sure you comment your code well, so that at a later date you (or another developer) will know exactly to which SQL implementation you were writing.

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 tutorialthe 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 bellow some commonly used text-manipulation functions.

Table 11.1. Commonly Used Text-Manipulation Functions

 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

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 Tutorial 11, "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 |
+---------+-----------+

Numeric Manipulation Functions

Table 11.3. Commonly Used Numeric Manipulation Functions

Summary

In this tutorial, you learned how to use SQL's data manipulation functions, and paid special attention to working with dates.

[Next]