This tutorial assumes that you have access to the MySQL command prompt and uses the people table created in the Create Tutorial and the payroll table referred to in the Join Queries Tutorial .
MySQL provides a number of functions that may be used on returned results.
String Functions
MySQL string functions are similar to the PHP functions covered in the PHP Strings tutorial. Below are some of the more useful ones.
Like
LIKE compares a returned string to a supplied string pattern that may include wildcards.
The % percentage character matches any number of unspecified characters in the string.
The _ underscore character matches a single unspecified character in the string.
Example of Using Like
mysql> SELECT lastname FROM people WHERE lastname LIKE 'Clark%';
+--------+
|lastname|
+--------+
|Clark |
|Clarkson|
+--------+
2 rows in set (0.01 sec)
mysql>
Upper() and Lower()
These functions will return uppercase and lowercase versions of a result respectively.
LOWER is useful in a WHERE clause when you can't be certain that upper or lower case has been used consistently with an attribute.
Examples of Using Upper and Lower
mysql> SELECT UPPER(address) FROM people;
+--------------+
|UPPER(address)|
+--------------+
|MAIN STREET |
|NORTH STREET |
|WEST STREET |
|FERRY ROAD |
+--------------+
4 rows in set (0.02 sec)
mysql> SELECT country FROM people WHERE LOWER(country) = 'uk';
+-------+
|country|
+-------+
|UK |
|UK |
+-------+
4 rows in set (0.02 sec)
mysql>
Concat()
CONCAT returns two or more strings or attributes concatenated.
Example of Using Concat
mysql> SELECT CONCAT(firstname, ' ', lastname) FROM people;
+--------------------------------+
|CONCAT(firstname, ' ', lastname)|
+--------------------------------+
|Dave Clark |
|Mary Smith |
|Dave Smith |
|Charles Clarkson |
+--------------------------------+
4 rows in set (0.03 sec)