Using Operators with Strings


In the previous lesson, you saw how arithmetic and comparison operations were performed on numeric values. Now you'll see the ways in which you can work with strings in MySQL.

Strings

A string is simply a collection of characters treated as a single data item, either returned from a database column that contains characters or enclosed in single quotes in a SQL statement.


Comparison Operators

The comparison operators you are already familiar with translate to string values in a logical way. The equals and not equals operators work almost as you would expect, but MySQL is not case sensitive when comparing characters.

The following query shows that MySQL produces search results on a string comparison even if the case of the text does not match:

 mysql> SELECT first_name, last_name     -> FROM customer_contacts     -> WHERE first_name IN ('CHARLES','marie'); +------------+-----------+ | first_name | last_name | +------------+-----------+ | Charles    | Darwin    | | Marie      | Curie     | +------------+-----------+ 2 rows in set (0.00 sec) 


To force a case-sensitive string comparison, use the BINARY operator to instruct MySQL to compare the characters in the string using their underlying ASCII values rather than just their letters.

For example, you know that the string abc will match ABC by default, as shown here:

 mysql> SELECT ' abc' = 'ABC'; +---------------+ | 'abc' = 'ABC' | +---------------+ |             1 | +---------------+ 1 row in set (0.00 sec) 


However, if you add the BINARY keyword before the expression, the case of each character becomes important.

 mysql> SELECT BINARY 'abc' = 'ABC'; +----------------------+ | BINARY 'abc' = 'ABC' | +----------------------+ |                    0 | +----------------------+ 1 row in set (0.00 sec) 


BINARY Ordering

When you use an ORDER BY clause on a string column, results are returned alphabetically, regardless of case. For case-sensitive ordering, use ORDER BY BINARY column.


The greater than and less than operators perform an alphabetical comparison between two strings. For instance, cat is a lower value than dog.

The following query selects data from the customer_contacts table based on a range of values using the BETWEEN operator:

 mysql> SELECT first_name, last_name     -> FROM customer_contacts     -> WHERE first_name BETWEEN 'C' and 'J'     -> ORDER BY first_name; +------------+-----------+ | first_name | last_name | +------------+-----------+ | Charles    | Darwin    | | Franklin   | Roosevelt | | George     | Gershwin  | +------------+-----------+ 3 rows in set (0.00 sec) 


Note that the result from this query does not include records with the first name John. Although the first letter falls within the specified range, John is considered a greater value than simply the letter J because it contains more characters.

Compare this to the following query with the range specified using two letters. This time, the John record is returned because it falls within the range CA to JZ.0

 mysql> SELECT first_name, last_name     -> FROM customer_contacts     -> WHERE first_name BETWEEN 'CA' and 'JZ'     -> ORDER BY first_name; +------------+-----------+ | first_name | last_name | +------------+-----------+ | Charles    | Darwin    | | Franklin   | Roosevelt | | George     | Gershwin  | | John       | Lennon    | +------------+-----------+ 4 rows in set (0.00 sec) 


ASCII Comparisons

Because the letters a to z have higher ASCII values than A to Z, a capital letter is considered a lower value than its lower-case equivalent if compared using the BINARY operator.


Wildcards

The LIKE operator works just like the = operator but enables you to specify wildcards in the string to be matched. An underscore character matches any single character, whereas the percent symbol matches any number of characters.

For instance, to find all the contacts that have a last name beginning with L, you can use the following query:

 mysql> SELECT last_name     -> FROM customer_contacts     -> WHERE last_name LIKE 'L%'; +-----------+ | last_name | +-----------+ | Lincoln   | | Lennon    | +-----------+ 2 rows in set (0.00 sec) 


Case Sensitivity

As with other string operators, LIKE is not case sensitive unless you also use the BINARY operator.


You can use more than one wildcard character in a condition, if desired. The following example finds only names that contain the r and end with in:

 mysql> SELECT last_name     -> FROM customer_contacts     -> WHERE last_name LIKE '%r%in'; +-----------+ | last_name | +-----------+ | Darwin    | | Franklin  | | Gershwin  | +-----------+ 3 rows in set (0.00 sec)  


The underscore character matches exactly one character, so using it in a wildcard expression restricts the values that will be matched using the position of letters within a string instead of simply the occurrence of a letter.

Changing the previous example as follows returns only names that contain the letter r in the third position and also end with in. This time, Franklin is excluded from the results.

 mysql> SELECT last_name     -> FROM customer_contacts     -> WHERE last_name LIKE '__r%in'; +-----------+ | last_name | +-----------+ | Darwin    | | Gershwin  | +-----------+ 2 rows in set (0.00 sec) 


NOT LIKE Use NOT LIKE with a wildcard pattern to exclude matching records from the query result.


Type Conversion

The idea of performing arithmetic operations on strings is a peculiar one. Although you will probably never have cause to do this, it is important to understand how MySQL handles expressions when the arguments are the wrong data type.

When you try to treat a string as a number, MySQL does its best to convert the value into the closest numeric equivalent. For example, if you add two numbers that are contained in quotes, MySQL returns the correct integer result even though these values are strings.

 mysql> SELECT '100' + 45; +------------+ | '100' + 45 | +------------+ |        145 | +------------+ 1 row in set (0.00 sec) 


When non-numeric text appears after a number, this is simply dropped.

 mysql> SELECT '100 apples' + '45 bananas'; +-----------------------------+ | '100 apples' + '45 bananas' | +-----------------------------+ |                         145 | +-----------------------------+ 1 row in set (0.00 sec) 


However, if text appears before a number in a string, its value is zero.

 mysql> SELECT 'USD 100' + 10; +----------------+ | 'USD 100' + 10 | +----------------+ |             10 | +----------------+ 1 row in set (0.00 sec) 


If the string contains text between two numbers, only the first number is used. In the following example, the string actually contains a numeric expression. Note that this is not evaluated; only the first number in the stringin this case, 4is taken into account.

 mysql> SELECT '4 * 2' + 1; +-------------+ | '4 * 2' + 1 | +-------------+ |           5 | +-------------+ 1 row in set (0.00 sec) 





Sams Teach Yourself MySQL in 10 Minutes
Sams Teach Yourself MySQL in 10 Minutes
ISBN: 0672328631
EAN: 2147483647
Year: 2006
Pages: 165
Authors: Chris Newman

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net