String-comparison operators and functions

Table 3-3 shows examples of the MySQL string-comparison operators and functions. Many of the MySQL string functions shown here are similar to PHP functions, which were introduced in Chapter 2.

Table 3-3. Using string comparison functions and operators
Statement Output
SELECT 'Apple' LIKE 'A%';
SELECT 'Apple' LIKE 'App%';
SELECT 'Apple' LIKE 'A%l%';
SELECT concat('con','cat');
SELECT length('Apple');
SELECT locate('pp','Apple');
SELECT substring('Apple',2,3);
SELECT ltrim('  Apple');
SELECT rtrim('Apple  ');
SELECT trim('  Apple  ');
SELECT space(3);
'   '
SELECT strcmp('a','a');
SELECT strcmp('a','b');
SELECT strcmp('b','a');
SELECT lower('Apple');
SELECT upper('Apple');

The string functions work as follows:

  • The string-comparison function LIKE is useful. The % character represents any number of unspecified characters, are generally known as wildcards. So, for example, the comparison of the string 'Apple' LIKE 'A%' is 1 (true), as is the comparison of 'Apple' LIKE 'App%'. The underscore character can be used to match a single unspecified, wildcard character; for example, 'Apple' LIKE 'Appl_' is true, while 'Appl' LIKE 'Appl_' is false.

  • concat( ) joins or concatenates two strings together, so the result of calling concat( ) with two string parameters is a single string consisting of the parameters.

  • length( ) returns the length of the string in characters.

  • locate( ) returns the location of the first string parameter in the second string parameter. If the string doesn't occur, the result is 0.

  • substring( ) returns part of the string passed as the first parameter. The string that is returned begins at the offset supplied as the second parameter and is of the length supplied as the third parameter.

  • ltrim( ) removes any left-padding space characters from the string parameter and returns the left-trimmed string.

  • rtrim( )removes any right-padding space characters from the string parameter and returns the right-trimmed string.

  • trim( ) performs the function of both ltrim( ) and rtrim( ); that is, any leading or trailing spaces are removed, and the trimmed string is returned.

  • space( ) returns a string consisting of spaces of the length of the integer parameter.

  • strcmp( ) compares two string parameters. If they are identical, it returns 0. If the first string is alphabetically less than the second, it returns a negative number. If the first string is alphabetically greater than the second, it returns a positive number. Uppercase characters are less than lowercase characters.

  • lower( ) converts the string parameter to lowercase and returns the lowercase string.

  • upper( ) converts the string parameter to uppercase and returns the uppercase string.

While not detailed in Table 3-3, regular expressions can be used through the function regexp( ). For more on regular expressions in PHP, see Chapter 2.