The first group of functions I will demonstrate are those meant for manipulating the various text and character columns. Most of the functions in this category are listed in Table 6.1.
Table 6.1. These are some, but not all, of the functions you can use on text columns in MySQL.
Function and Usage
Returns the length of the string stored in the column.
Returns the leftmost x characters from a column.
Returns the rightmost x characters from a column.
Trims excess spaces from the beginning and end of the stored string.
Capitalizes the entire stored string.
Turns the stored string into an alllowercase format.
SUBSTRING(text, start, length)
Returns length characters from column beginning with start (indexed from 1).
FIND_IN_SET (str, set)
Returns a positive number if str is found in set; returns 0 otherwise.
To use any function, you need to modify your query so that you specify to which column or columns the function should be applied.
SELECT FUNCTION(column) FROM tablename
To specify multiple columns, you can write a query like either of these:
SELECT *, FUNCTION(column) FROM tablename
SELECT column1, FUNCTION(column2),
column3 FROM tablename
While the function names themselves are case-insensitive, I will continue to write them in an all-capitalized format, to help distinguish them from table and column names (as I do with SQL terms). One important rule with functions is that you cannot have spaces between the function name and the opening parenthesis for that function, although spaces within the parentheses are acceptable.
I'll repeat this last bit because it's a common cause of problems: do not have a space between a function's name and its opening parenthesis!
To format text:
Open the mysql
client and select the accounting
database (Figure 6.1
Figure 6.1. Once again, the mysql client and the accounting database will be the basis of all examples in this chapter.
As in the preceding chapter, from here on I will assume you are already using the accounting
database within the mysql
Remove all extraneous white spaces from the client names (Figure 6.2
Figure 6.2. The TRIM() function eliminates superfluous spaces from the beginnings and ends of returned values.
SELECT TRIM(client_name) FROM clients;
function will automatically strip white spaces (spaces, tabs, and returns) from both the beginning and end of a string.
View just the area codes for all client phone numbers (Figure 6.3
Figure 6.3. This application of the SUBSTRING() function returns only the second through fifth characters of a stored value.
SELECT SUBSTRING(client_phone, 2, 3) FROM clients WHERE client_phone IS NOT NULL;
function returns part of a string. Its first argument is the literal string or column name. Its second argument is where to begin in that string, counting from 1. Since the first character in a phone number should be the opening parenthesis, I want to begin at the second character. The third argument, which is optional, dictates how many characters should be returned (all of the remaining characters are returned if this argument is omitted).
Find the longest expense category name (Figure 6.4
Figure 6.4. You can even use a function within an ORDER BY clause.
SELECT LENGTH(expense_category), expense_category FROM
expense_categories ORDER BY
This query first gathers all of the expense categories, along with their length. Then it sorts this data from the longest category on down. Finally, only the first (i.e., the longest) record is returned.
A query like that in Step 4 (also Figure 6.4) may be useful for helping to fine-tune your column lengths once your database has some records in it.
You can use most of the MySQL functions while running queries other than SELECT. Most frequently you might use a function to format data used in an INSERT.
Two other useful functions are not mentioned here because of the complexity of their syntax and use. The first is LOCATE(), which returns the starting position of one character or string found within another. The second is REPLACE(), which returns a string after replacing some characters or text with other characters and text. Both are detailed in the manual, of course.
Functions can be equally applied to both columns and literal strings. For example, the following is perfectly acceptable: SELECT UPPER('makemebig')
New in MySQL 4.1 is the ability to convert some text from one character set to another. To do so, use the CONVERT() function. Mind you, this won't translate text from one language to another; it just changes the characters used.