Expressions can be used at several points in SQL statements, such as
in the ORDER BY or HAVING
clauses of SELECT statements, in the
WHERE clause of a SELECT,
DELETE, or UPDATE statement,
or in SET statements. Expressions can be written
using literal values, column values, NULL,
functions, and operators. This chapter describes the functions and
operators that are allowed for writing expressions in MySQL.
An expression that contains NULL always produces
a NULL value unless otherwise indicated in the
documentation for a particular function or operator.
Note: By default, there must be no
whitespace between a function name and the parenthesis following it.
This helps the MySQL parser distinguish between function calls and
references to tables or columns that happen to have the same name as
a function. However, spaces around function arguments are permitted.
You can tell the MySQL server to accept spaces after function names
by starting it with the --sql-mode=IGNORE_SPACE
option. Individual client programs can request this behavior by
using the CLIENT_IGNORE_SPACE option for
mysql_real_connect(). In either case, all
function names become reserved words. See
Section 5.2.2, “The Server SQL Mode”.
For the sake of brevity, most examples in this chapter display the
output from the mysql program in abbreviated
form. Instead of showing examples in this format:
mysql> SELECT MOD(29,9);
+-----------+
| mod(29,9) |
+-----------+
| 2 |
+-----------+
1 rows in set (0.00 sec)
Operator precedences are shown in the following list, from
lowest precedence to the highest. Operators that are shown
together on a line have the same precedence.
:=
||, OR, XOR
&&, AND
BETWEEN, CASE, WHEN, THEN, ELSE
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
|
&
<<, >>
-, +
*, /, DIV, %, MOD
^
- (unary minus), ~ (unary bit inversion)
!, NOT
BINARY, COLLATE
12.1.2. Parentheses
( ... )
Use parentheses to force the order of evaluation in an
expression. For example:
Comparison operations result in a value of 1
(TRUE), 0 (FALSE), or
NULL. These operations work for both numbers
and strings. Strings are automatically converted to numbers and
numbers to strings as necessary.
Some of the functions in this section (such as
LEAST() and GREATEST())
return values other than 1 (TRUE),
0 (FALSE), or NULL.
However, the value they return is based on comparison operations
performed as described by the following rules.
MySQL compares values using the following rules:
If one or both arguments are NULL, the
result of the comparison is NULL, except
for the NULL-safe
<=> equality comparison operator.
If both arguments in a comparison operation are strings,
they are compared as strings.
If both arguments are integers, they are compared as
integers.
Hexadecimal values are treated as binary strings if not
compared to a number.
If one of the arguments is a TIMESTAMP or
DATETIME column and the other argument is
a constant, the constant is converted to a timestamp before
the comparison is performed. This is done to be more
ODBC-friendly. Note that this is not done for arguments in
IN()! To be safe, always use complete
datetime/date/time strings when doing comparisons.
In all other cases, the arguments are compared as
floating-point (real) numbers.
By default, string comparisons are not case sensitive and use
the current character set (cp1252 Latin1 by default, which also
works well for English).
To convert a value to a specific type for comparison purposes,
you can use the CAST() function. String
values can be converted to a different character set using
CONVERT(). See
Section 12.8, “Cast Functions and Operators”.
The following examples illustrate conversion of strings to
numbers for comparison operations:
Note that when you are comparing a string column with a number,
MySQL cannot use an index on the column to quickly look up the
value. If str_col is an indexed
string column, the index cannot be used when performing the
lookup in the following statement:
SELECT * FROM tbl_name WHERE str_col=1;
The reason for this is that there are many different strings
that may convert to the value 1:
'1', ' 1',
'1a', ...
NULL-safe equal. This operator performs
an equality comparison like the =
operator, but returns 1 rather than
NULL if both operands are
NULL, and 0 rather
than NULL if one operand is
NULL.
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
-> 0, 0, 1
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
-> 1, 1, 0
To be able to work well with ODBC programs, MySQL supports
the following extra features when using IS
NULL:
You can find the row that contains the most recent
AUTO_INCREMENT value by issuing a
statement of the following form immediately after
generating the value:
For DATE and
DATETIME columns that are declared as
NOT NULL, you can find the special
date '0000-00-00' by using a
statement like this:
SELECT * FROM tbl_name WHERE date_column IS NULL
This is needed to get some ODBC applications to work
because ODBC does not support a
'0000-00-00' date value.
expr BETWEEN
min AND
max
If expr is greater than or equal
to min and
expr is less than or equal to
max, BETWEEN
returns 1, otherwise it returns
0. This is equivalent to the expression
(min <=
expr AND
expr <=
max) if all the
arguments are of the same type. Otherwise type conversion
takes place according to the rules described at the
beginning of this section, but applied to all the three
arguments. Note: Before
MySQL 4.0.5, arguments were converted to the type of
expr instead.
mysql> SELECT 1 BETWEEN 2 AND 3;
-> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
-> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
-> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
-> 0
expr NOT BETWEEN
min AND
max
This is the same as NOT
(expr BETWEEN
min AND
max).
COALESCE(value,...)
Returns the first non-NULL value in the
list, or NULL if there are no
non-NULL values.
GREATEST() returns
NULL only if all arguments are
NULL.
Before MySQL 3.22.5, you can use MAX()
instead of GREATEST().
expr IN
(value,...)
Returns 1 if
expr is equal to any of the
values in the IN list, else returns
0. If all values are constants, they are
evaluated according to the type of
expr and sorted. The search for
the item then is done using a binary search. This means
IN is very quick if the
IN value list consists entirely of
constants. Otherwise, type conversion takes place according
to the rules described at the beginning of this section, but
applied to all the arguments.
mysql> SELECT 2 IN (0,3,5,'wefwf');
-> 0
mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');
-> 1
The number of values in the IN list is
only limited by the max_allowed_packet
value.
To comply with the SQL standard, from MySQL 4.1.0 on
IN returns NULL not
only if the expression on the left hand side is
NULL, but also if no match is found in
the list and one of the expressions in the list is
NULL.
A comparison of NULL values using
= is always false.
The ISNULL() function shares some special
behaviors with the IS NULL comparison
operator. See the description of IS NULL.
INTERVAL(N,N1,N2,N3,...)
Returns 0 if N
< N1, 1 if
N <
N2 and so on or
-1 if N is
NULL. All arguments are treated as
integers. It is required that N1
< N2 <
N3 < ...
< Nn for this function to work
correctly. This is because a binary search is used (very
fast).
LEAST() returns NULL
only if all arguments are NULL.
Before MySQL 3.22.5, you can use MIN()
instead of LEAST().
Note that the preceding conversion rules can produce strange
results in some borderline cases:
mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);
-> -9223372036854775808
This happens because MySQL reads
9223372036854775808.0 in an integer
context. The integer representation is not good enough to
hold the value, so it wraps to a signed integer.
12.1.4. Logical Operators
In SQL, all logical operators evaluate to
TRUE, FALSE, or
NULL (UNKNOWN). In MySQL,
these are implemented as 1 (TRUE), 0
(FALSE), and NULL. Most of
this is common to different SQL database servers, although some
servers may return any non-zero value for
TRUE.
NOT, !
Logical NOT. Evaluates to 1 if the
operand is 0, to 0 if
the operand is non-zero, and NOT NULL
returns NULL.
mysql> SELECT NOT 10;
-> 0
mysql> SELECT NOT 0;
-> 1
mysql> SELECT NOT NULL;
-> NULL
mysql> SELECT ! (1+1);
-> 0
mysql> SELECT ! 1+1;
-> 1
The last example produces 1 because the
expression evaluates the same way as
(!1)+1.
AND, &&
Logical AND. Evaluates to 1 if all
operands are non-zero and not NULL, to
0 if one or more operands are
0, otherwise NULL is
returned.
Please note that MySQL versions prior to 4.0.5 stop
evaluation when a NULL is encountered,
rather than continuing the process to check for possible
0 values. This means that in these
versions, SELECT (NULL AND 0) returns
NULL instead of 0. As
of MySQL 4.0.5, the code has been re-engineered so that the
result is always as prescribed by the SQL standards while
still using the optimization wherever possible.
OR, ||
Logical OR. When both operands are
non-NULL, the result is
1 if any operand is non-zero, and
0 otherwise. With a
NULL operand, the result is
1 if the other operand is non-zero, and
NULL otherwise. If both operands are
NULL, the result is
NULL.
Logical XOR. Returns NULL if either
operand is NULL. For
non-NULL operands, evaluates to
1 if an odd number of operands is
non-zero, otherwise 0 is returned.
CASE value WHEN
[compare-value] THEN
result [WHEN
[compare-value] THEN
result ...] [ELSE
result] END
CASE WHEN [condition] THEN
result [WHEN
[condition] THEN
result ...] [ELSE
result] END
The first version returns the
result where
value=compare-value.
The second version returns the result for the first condition
that is true. If there was no matching result value, the
result after ELSE is returned, or
NULL if there is no ELSE
part.
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
-> WHEN 2 THEN 'two' ELSE 'more' END;
-> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
-> 'true'
mysql> SELECT CASE BINARY 'B'
-> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
-> NULL
Before MySQL 4.1, the type of the return value
(INTEGER, DOUBLE, or
STRING) is the same as the type of the
first returned value (the expression after the first
THEN). From MySQL 4.1.0, the default return
type is the compatible aggregated type of all return values.
Note that CASE evaluation depends also on
the context in which it is used. If used in string context,
the result is returned as a string. If used in numeric
context, the result is returned decimal, real, or integer
value.
CASE was added in MySQL 3.23.3.
IF(expr1,expr2,expr3)
If expr1 is TRUE
(expr1 <>
0 and expr1
<> NULL) then IF() returns
expr2; otherwise it returns
expr3. IF()
returns a numeric or string value, depending on the context in
which it is used.
If only one of expr2 or
expr3 is explicitly
NULL, the result type of the
IF() function is the type of
non-NULL expression. (This behavior was
implemented in MySQL 4.0.3.)
expr1 is evaluated as an integer
value, which means that if you are testing floating-point or
string values, you should do so using a comparison operation.
In the first case shown, IF(0.1) returns
0 because 0.1 is
converted to an integer value, resulting in a test of
IF(0). This may not be what you expect. In
the second case, the comparison tests the original
floating-point value to see whether it is non-zero. The result
of the comparison is used as an integer.
The default return type of IF() (which may
matter when it is stored into a temporary table) is calculated
in MySQL 3.23 as follows:
Expression
Return Value
expr2 or expr3
returns a string
string
expr2 or expr3
returns a floating-point value
floating-point
expr2 or expr3
returns an integer
integer
If expr2 and
expr3 are both strings, the result
is case sensitive if either string is case sensitive (starting
from MySQL 3.23.51).
IFNULL(expr1,expr2)
If expr1 is not
NULL, IFNULL() returns
expr1; otherwise it returns
expr2. IFNULL()
returns a numeric or string value, depending on the context in
which it is used.
In MySQL 4.0.6 and above, the default result value of
IFNULL(expr1,expr2)
is the more “general” of the two expressions, in
the order STRING, REAL,
or INTEGER. The difference from earlier
MySQL versions is mostly notable when you create a table based
on expressions or MySQL has to internally store a value from
IFNULL() in a temporary table.
CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
As of MySQL 4.0.6, the type for the test
column is CHAR(4), whereas in earlier
versions the type would be BIGINT.
NULLIF(expr1,expr2)
Returns NULL if
expr1 =
expr2 is true, otherwise
returns expr1. This is the same as
CASE WHEN expr1 =
expr2 THEN NULL ELSE
expr1 END.
String-valued functions return NULL if the
length of the result would be greater than the value of the
max_allowed_packet system variable. See
Section 7.5.2, “Tuning Server Parameters”.
For functions that operate on string positions, the first position
is numbered 1.
ASCII(str)
Returns the numeric value of the leftmost character of the
string str. Returns
0 if str is the
empty string. Returns NULL if
str is NULL.
ASCII() works for characters with numeric
values from 0 to 255.
Returns a string representation of the binary value of
N, where
N is a longlong
(BIGINT) number. This is equivalent to
CONV(N,10,2).
Returns NULL if
N is NULL.
mysql> SELECT BIN(12);
-> '1100'
BIT_LENGTH(str)
Returns the length of the string
str in bits.
mysql> SELECT BIT_LENGTH('text');
-> 32
BIT_LENGTH() was added in MySQL 4.0.2.
CHAR(N,... [USING
charset])
CHAR() interprets each argument
N as an integer and returns a
string consisting of the characters given by the code values
of those integers. NULL values are skipped.
CHAR() returns a string in the connection
character set. As of MySQL 4.1.16, the optional
USING clause may be used to produce a
string in a given character set:
Returns the length of the string
str, measured in characters. A
multi-byte character counts as a single character. This means
that for a string containing five two-byte characters,
LENGTH() returns 10,
whereas CHAR_LENGTH() returns
5.
CHARACTER_LENGTH(str)
CHARACTER_LENGTH() is a synonym for
CHAR_LENGTH().
COMPRESS(string_to_compress)
Compresses a string. This function requires MySQL to have been
compiled with a compression library such as
zlib. Otherwise, the return value is always
NULL. The compressed string can be
uncompressed with UNCOMPRESS().
The compressed string contents are stored the following way:
Empty strings are stored as empty strings.
Non-empty strings are stored as a four-byte length of the
uncompressed string (low byte first), followed by the
compressed string. If the string ends with space, an extra
‘.’ character is added to
avoid problems with endspace trimming should the result be
stored in a CHAR or
VARCHAR column. (Use of
CHAR or VARCHAR to
store compressed strings is not recommended. It is better
to use a BLOB column instead.)
COMPRESS() was added in MySQL 4.1.1.
CONCAT(str1,str2,...)
Returns the string that results from concatenating the
arguments. Returns NULL if any argument is
NULL. May have one or more arguments. If
all arguments are non-binary strings, the result is a
non-binary string. If the arguments include any binary
strings, the result is a binary string. A numeric argument is
converted to its equivalent binary string form; if you want to
avoid that you can use explicit type cast, like in this
example: SELECT CONCAT(CAST(int_col AS CHAR),
char_col)
CONCAT_WS() stands for CONCAT With
Separator and is a special form of
CONCAT(). The first argument is the
separator for the rest of the arguments. The separator is
added between the strings to be concatenated. The separator
can be a string as can the rest of the arguments. If the
separator is NULL, the result is
NULL. The function skips any
NULL values after the separator argument.
Before MySQL 4.0.14, CONCAT_WS() skips
empty strings as well as NULL values.
CONV(N,from_base,to_base)
Converts numbers between different number bases. Returns a
string representation of the number
N, converted from base
from_base to base
to_base. Returns
NULL if any argument is
NULL. The argument
N is interpreted as an integer, but
may be specified as an integer or a string. The minimum base
is 2 and the maximum base is
36. If to_base
is a negative number, N is regarded
as a signed number. Otherwise, N is
treated as unsigned. CONV() works with
64-bit precision.
Returns str1 if
N = 1,
str2 if
N = 2, and so
on. Returns NULL if
N is less than 1
or greater than the number of arguments.
ELT() is the complement of
FIELD().
Returns a string in which for every bit set in the value
bits, you get an
on string and for every reset bit
you get an off string. Bits in
bits are examined from right to
left (from low-order to high-order bits). Strings are added to
the result from left to right, separated by the
separator string (the default being
the comma character ‘,’). The
number of bits examined is given by
number_of_bits (defaults to 64).
Returns the index of str in the
str1,
str2,
str3, ... list.
Returns 0 if str
is not found.
If all arguments to FIELD() are strings,
all arguments are compared as strings. If all arguments are
numbers, they are compared as numbers. Otherwise, the
arguments are compared as double.
If str is NULL,
the return value is 0 because
NULL fails equality comparison with any
value. FIELD() is the complement of
ELT().
Returns a value in the range of 1 to
N if the string
str is in the string list
strlist consisting of
N substrings. A string list is a
string composed of substrings separated by
‘,’ characters. If the first
argument is a constant string and the second is a column of
type SET, the
FIND_IN_SET() function is optimized to use
bit arithmetic. Returns 0 if
str is not in
strlist or if
strlist is the empty string.
Returns NULL if either argument is
NULL. This function does not work properly
if the first argument contains a comma
(‘,’) character.
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
FORMAT(X,D)
Formats the number X to a format
like '#,###,###.##', rounded to
D decimals, and returns the result
as a string. See For details, see
Section 12.9.4, “Miscellaneous Functions”.
HEX(N_or_S)
If N_OR_S is a number, returns a
string representation of the hexadecimal value of
N, where
N is a longlong
(BIGINT) number. This is equivalent to
CONV(N,10,16).
From MySQL 4.0.1 and up, if N_OR_S
is a string, returns a hexadecimal string representation of
N_OR_S where each character in
N_OR_S is converted to two
hexadecimal digits.
Returns the string str, with the
substring beginning at position pos
and len characters long replaced by
the string newstr. Returns the
original string if pos is not
within the length of the string. Replaces the rest of the
string from position pos is
len is not within the length of the
rest of the string. Returns NULL if any
argument is null.
Returns the position of the first occurrence of substring
substr in string
str. This is the same as the
two-argument form of LOCATE(), except that
the order of the arguments is reversed.
This function is multi-byte safe. In MySQL 3.23, this function
is case sensitive. For 4.0 on, it is case sensitive only if
either argument is a binary string.
LCASE(str)
LCASE() is a synonym for
LOWER().
LEFT(str,len)
Returns the leftmost len characters
from the string str.
mysql> SELECT LEFT('foobarbar', 5);
-> 'fooba'
LENGTH(str)
Returns the length of the string
str, measured in bytes. A
multi-byte character counts as multiple bytes. This means that
for a string containing five two-byte characters,
LENGTH() returns 10,
whereas CHAR_LENGTH() returns
5.
mysql> SELECT LENGTH('text');
-> 4
LOAD_FILE(file_name)
Reads the file and returns the file contents as a string. The
file must be located on the server, you must specify the full
pathname to the file, and you must have the
FILE privilege. The file must be readable
by all and its size less than
max_allowed_packet bytes.
If the file does not exist or cannot be read because one of
the preceding conditions is not satisfied, the function
returns NULL.
mysql> UPDATE tbl_name
SET blob_column=LOAD_FILE('/tmp/picture')
WHERE id=1;
Before MySQL 3.23, you must read the file inside your
application and create an INSERT statement
to update the database with the file contents. If you are
using the MySQL++ library, one way to do this can be found in
the MySQL++ manual, available at
http://tangentsoft.net/mysql++/doc/.
LOCATE(substr,str),
LOCATE(substr,str,pos)
The first syntax returns the position of the first occurrence
of substring substr in string
str. The second syntax returns the
position of the first occurrence of substring
substr in string
str, starting at position
pos. Returns 0
if substr is not in
str.
This function is multi-byte safe. In MySQL 3.23, this function
is case sensitive. For 4.0 on, it is case sensitive only if
either argument is a binary string.
LOWER(str)
Returns the string str with all
characters changed to lowercase according to the current
character set mapping (the default is cp1252 Latin1).
Returns the string str, left-padded
with the string padstr to a length
of len characters. If
str is longer than
len, the return value is shortened
to len characters.
Returns the string str with leading
space characters removed.
mysql> SELECT LTRIM(' barbar');
-> 'barbar'
This function is multi-byte safe.
MAKE_SET(bits,str1,str2,...)
Returns a set value (a string containing substrings separated
by ‘,’ characters) consisting
of the strings that have the corresponding bit in
bits set.
str1 corresponds to bit 0,
str2 to bit 1, and so on.
NULL values in
str1,
str2, ... are
not appended to the result.
MID(str,pos,len)
is a synonym for
SUBSTRING(str,pos,len).
OCT(N)
Returns a string representation of the octal value of
N, where
N is a longlong
(BIGINT)number. This is equivalent to
CONV(N,10,8). Returns
NULL if N is
NULL.
mysql> SELECT OCT(12);
-> '14'
OCTET_LENGTH(str)
OCTET_LENGTH() is a synonym for
LENGTH().
ORD(str)
If the leftmost character of the string
str is a multi-byte character,
returns the code for that character, calculated from the
numeric values of its constituent bytes using this formula:
If the leftmost character is not a multi-byte character,
ORD() returns the same value as the
ASCII() function.
mysql> SELECT ORD('2');
-> 50
POSITION(substr IN
str)
POSITION(substr IN
str) is a synonym for
LOCATE(substr,str).
QUOTE(str)
Quotes a string to produce a result that can be used as a
properly escaped data value in an SQL statement. The string is
returned surrounded by single quotes and with each instance of
single quote (‘'’), backslash
(‘\’), ASCII
NUL, and Control-Z preceded by a backslash.
If the argument is NULL, the return value
is the word “NULL” without surrounding single
quotes. The QUOTE() function was added in
MySQL 4.0.3.
Returns the string str with all
occurrences of the string from_str
replaced by the string to_str.
REPLACE() performs a case-sensitive match
when searching for from_str.
Returns the string str with the
order of the characters reversed.
mysql> SELECT REVERSE('abc');
-> 'cba'
This function is multi-byte safe.
RIGHT(str,len)
Returns the rightmost len
characters from the string str.
mysql> SELECT RIGHT('foobarbar', 4);
-> 'rbar'
This function is multi-byte safe.
RPAD(str,len,padstr)
Returns the string str,
right-padded with the string padstr
to a length of len characters. If
str is longer than
len, the return value is shortened
to len characters.
Returns the string str with
trailing space characters removed.
mysql> SELECT RTRIM('barbar ');
-> 'barbar'
This function is multi-byte safe.
SOUNDEX(str)
Returns a soundex string from str.
Two strings that sound almost the same should have identical
soundex strings. A standard soundex string is four characters
long, but the SOUNDEX() function returns an
arbitrarily long string. You can use
SUBSTRING() on the result to get a standard
soundex string. All non-alphabetic characters in
str are ignored. All international
alphabetic characters outside the A-Z range are treated as
vowels.
Note: This function
implements the original Soundex algorithm, not the more
popular enhanced version (also described by D. Knuth). The
difference is that original version discards vowels first and
then duplicates, whereas the enhanced version discards
duplicates first and then vowels.
expr1 SOUNDS LIKE
expr2
This is the same as
SOUNDEX(expr1) =
SOUNDEX(expr2). It is
available beginning with MySQL 4.1.0.
SPACE(N)
Returns a string consisting of N
space characters.
mysql> SELECT SPACE(6);
-> ' '
SUBSTRING(str,pos),
SUBSTRING(str FROM
pos),
SUBSTRING(str,pos,len),
SUBSTRING(str FROM
pos FOR
len)
The forms without a len argument
return a substring from string str
starting at position pos. The forms
with a len argument return a
substring len characters long from
string str, starting at position
pos. The forms that use
FROM are standard SQL syntax. Beginning
with MySQL 4.1.0, it is possible to use a negative value for
pos. In this case, the beginning of
the substring is pos characters
from the end of the string, rather than the beginning. A
negative value may be used for pos
in any of the forms of this function.
Note that if you use a value less than than 1 for
len, the result is always an empty
string.
SUBSTR() is a synonym for
SUBSTRING(), added in MySQL 4.1.1.
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string
str before
count occurrences of the delimiter
delim. If
count is positive, everything to
the left of the final delimiter (counting from the left) is
returned. If count is negative,
everything to the right of the final delimiter (counting from
the right) is returned. SUBSTRING_INDEX()
performs a case-sensitive match when searching for
delim.
Returns the string str with all
remstr prefixes and/or suffixes
removed. If none of the specifiers BOTH,
LEADING, or TRAILING is
given, BOTH is assumed.
remstr is optional and, if not
specified, spaces are removed.
mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
This function is multi-byte safe.
UCASE(str)
UCASE() is a synonym for
UPPER().
UNCOMPRESS(string_to_uncompress)
Uncompresses a string compressed by the
COMPRESS() function. If the argument is not
a compressed value, the result is NULL.
This function requires MySQL to have been compiled with a
compression library such as zlib.
Otherwise, the return value is always NULL.
Performs the opposite operation from
HEX(str). That
is, it interprets each pair of hexadecimal digits in the
argument as a number and converts it to the character
represented by the number. The resulting characters are
returned as a binary string.
CAST() is preferable, but it is unavailable
before MySQL 4.0.2.
If a string function is given a binary string as an argument,
the resulting string is also a binary string. A number converted
to a string is treated as a binary string. This affects only
comparisons.
Normally, if any expression in a string comparison is case
sensitive, the comparison is performed in case-sensitive
fashion.
expr LIKE
pat [ESCAPE
'escape-char']
Pattern matching using SQL simple regular expression
comparison. Returns 1
(TRUE) or 0
(FALSE). If either
expr or
pat is NULL,
the result is NULL.
The pattern need not be a literal string. For example, it
can be specified as a string expression or table column.
Per the SQL standard, LIKE performs
matching on a per-character basis, thus it can have
different results than the = comparison
operator:
With LIKE you can use the following two
wildcard characters in the pattern:
Character
Description
%
Matches any number of characters, even zero characters
_
Matches exactly one character
mysql> SELECT 'David!' LIKE 'David_';
-> 1
mysql> SELECT 'David!' LIKE '%D%v%';
-> 1
To test for literal instances of a wildcard character,
precede the character with the escape character. If you do
not specify the ESCAPE character,
‘\’ is assumed.
String
Description
\%
Matches one ‘%’ character
\_
Matches one ‘_’ character
mysql> SELECT 'David!' LIKE 'David\_';
-> 0
mysql> SELECT 'David_' LIKE 'David\_';
-> 1
To specify a different escape character, use the
ESCAPE clause:
mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
-> 1
The following two statements illustrate that string
comparisons are not case sensitive unless one of the
operands is a binary string:
mysql> SELECT 'abc' LIKE 'ABC';
-> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
-> 0
In MySQL, LIKE is allowed on numeric
expressions. (This is an extension to the standard SQL
LIKE.)
mysql> SELECT 10 LIKE '1%';
-> 1
Note: Because MySQL uses C
escape syntax in strings (for example,
‘\n’ to represent a newline
character), you must double any
‘\’ that you use in
LIKE strings. For example, to search for
‘\n’, specify it as
‘\\n’. To search for
‘\’, specify it as
‘\\\\’; this is because the
backslashes are stripped once by the parser and again when
the pattern match is made, leaving a single backslash to be
matched against.
expr NOT LIKE
pat [ESCAPE
'escape-char']
This is the same as NOT
(expr LIKE
pat [ESCAPE
'escape-char']).
expr NOT REGEXP
pat,
expr NOT RLIKE
pat
This is the same as NOT
(expr REGEXP
pat).
expr REGEXP
patexpr RLIKE
pat
Performs a pattern match of a string expression
expr against a pattern
pat. The pattern can be an
extended regular expression. The syntax for regular
expressions is discussed in Appendix G, MySQL Regular Expressions.
Returns 1 if
expr matches
pat; otherwise it returns
0. If either
expr or
pat is NULL,
the result is NULL.
RLIKE is a synonym for
REGEXP, provided for
mSQL compatibility.
The pattern need not be a literal string. For example, it
can be specified as a string expression or table column.
Note: Because MySQL uses
the C escape syntax in strings (for example,
‘\n’ to represent the newline
character), you must double any
‘\’ that you use in your
REGEXP strings.
As of MySQL 3.23.4, REGEXP is not case
sensitive, except when used with binary strings.
REGEXP and RLIKE use
the current character set (cp1252 Latin1 by default) when
deciding the type of a character.
Warning: These operators
are not multi-byte safe.
STRCMP(expr1,expr2)
STRCMP() returns 0 if
the strings are the same, -1 if the first
argument is smaller than the second according to the current
sort order, and 1 otherwise.
As of MySQL 4.0, STRCMP() uses the
current character set when performing comparisons. This
makes the default comparison behavior case insensitive
unless one or both of the operands are binary strings.
Before MySQL 4.0, STRCMP() is case
sensitive.
The usual arithmetic operators are available. Note that in the
case of -, +, and
*, the result is calculated with
BIGINT (64-bit) precision if both arguments
are integers. If one of the arguments is an unsigned integer,
and the other argument is also an integer, the result is an
unsigned integer. See Section 12.8, “Cast Functions and Operators”.
+
Addition:
mysql> SELECT 3+5;
-> 8
-
Subtraction:
mysql> SELECT 3-5;
-> -2
-
Unary minus. Changes the sign of the argument.
mysql> SELECT - 2;
-> -2
Note: If this operator is
used with a BIGINT, the return value is
also a BIGINT. This means that you should
avoid using – on integers that may
have the value of
–263.
The result of the last expression is incorrect because the
result of the integer multiplication exceeds the 64-bit
range of BIGINT calculations. (See
Section 11.2, “Numeric Types”.)
/
Division:
mysql> SELECT 3/5;
-> 0.60
Division by zero produces a NULL result:
mysql> SELECT 102/(1-1);
-> NULL
A division is calculated with BIGINT
arithmetic only if performed in a context where its result
is converted to an integer.
DIV
Integer division. Similar to FLOOR() but
safe with BIGINT values.
mysql> SELECT 5 DIV 2;
-> 2
DIV was implemented in MySQL 4.1.0.
12.4.2. Mathematical Functions
All mathematical functions return NULL in the
event of an error.
Returns the arc tangent of the two variables
X and
Y. It is similar to calculating
the arc tangent of Y /
X, except that the
signs of both arguments are used to determine the quadrant
of the result.
Computes a cyclic redundancy check value and returns a
32-bit unsigned value. The result is NULL
if the argument is NULL. The argument is
expected to be a string and (if possible) is treated as one
if it is not.
