Table 3-3. Using string comparison functions and operators
Statement | Output |
---|---|
SELECT 'Apple' LIKE 'A%'; |
1 |
SELECT 'Apple' LIKE 'App%'; |
1 |
SELECT 'Apple' LIKE 'A%l%'; |
1 |
SELECT concat('con','cat'); |
'concat' |
SELECT length('Apple'); |
5 |
SELECT locate('pp','Apple'); |
2 |
SELECT substring('Apple',2,3); |
'ppl' |
SELECT ltrim(' Apple'); |
'Apple' |
SELECT rtrim('Apple '); |
'Apple' |
SELECT trim(' Apple '); |
'Apple' |
SELECT space(3); |
' ' |
SELECT strcmp('a','a'); |
0 |
SELECT strcmp('a','b'); |
-1 |
SELECT strcmp('b','a'); |
1 |
SELECT lower('Apple'); |
'apple' |
SELECT upper('Apple'); |
'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_'
istrue
, while'Appl'
LIKE
'Appl_'
isfalse
. -
concat( )
joins or concatenates two strings together, so the result of callingconcat( )
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 bothltrim( )
andrtrim( )
; 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.