Text Functions

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.

Text Functions
Function and Usage	Purpose
LENGTH(text)	Returns the length of the string stored in the column.
LEFT(text, x)	Returns the leftmost x characters from a column.
RIGHT(text, x)	Returns the rightmost x characters from a column.
trIM(text)	Trims excess spaces from the beginning and end of the stored string.
UPPER(text)	Capitalizes the entire stored string.
LOWER(text)	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:

1.	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. [View full size image] USE accounting; As in the preceding chapter, from here on I will assume you are already using the accounting database within the mysql client.
2.	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; The trIM() function will automatically strip white spaces (spaces, tabs, and returns) from both the beginning and end of a string.
3.	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; The SUBSTRING() 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).
4.	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 LENGTH(expense_category) DESC LIMIT 1; 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.