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:
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
+----------------+------------------+ | 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 | +----------------+------------------+
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.
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:
SELECT cust_name, cust_contact FROM customers WHERE cust_contact = 'Y. Lie';
+-------------+--------------+ | cust_name | cust_contact | +-------------+--------------+
Now try the same search using the Soundex() function to match all contact names that sound similar to Y. Lie:
SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y Lie');
+-------------+--------------+ | cust_name | cust_contact | +-------------+--------------+ | Coyote Inc. | Y Lee | +-------------+--------------+
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
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.
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).
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.
SELECT cust_id, order_num FROM orders WHERE order_date = '2005-09-01';
+---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | +---------+-----------+
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
SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01';
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:
SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
+---------+-----------+ | cust_id | order_num | +---------+-----------+ | 10001 | 20005 | | 10003 | 20006 | | 10004 | 20007 | +---------+-----------+
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):
SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
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.
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.