String Functions
884
Note
There is also an
IF
statement, which differs from the
IF()
[883]
function
described here. See
Section 13.6.5.2, “
IF
Syntax”
.
•
IFNULL(expr1,expr2)
[884]
If
expr1
is not
NULL
,
IFNULL()
[884]
returns
expr1
; otherwise it returns
expr2
.
IFNULL()
[884]
returns a numeric or string value, depending on the context in which it is used.
mysql>
SELECT IFNULL(1,0);
-> 1
mysql>
SELECT IFNULL(NULL,10);
-> 10
mysql>
SELECT IFNULL(1/0,10);
-> 10
mysql>
SELECT IFNULL(1/0,'yes');
-> 'yes'
The default result value of
IFNULL(expr1,expr2)
[884]
is the more “general” of the two
expressions, in the order
STRING
,
REAL
, or
INTEGER
. Consider the case of a table based on
expressions or where MySQL must internally store a value returned by
IFNULL()
[884]
in a
temporary table:
mysql>
CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
mysql>
DESCRIBE tmp;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| test | varbinary(4) | NO | | | |
+-------+--------------+------+-----+---------+-------+
In this example, the type of the
test
column is
VARBINARY(4)
.
•
NULLIF(expr1,expr2)
[884]
Returns
NULL
if
expr1 = expr2
is true, otherwise returns
expr1
. This is the same as
CASE WHEN
expr1 = expr2 THEN NULL ELSE expr1 END
[883]
.
mysql>
SELECT NULLIF(1,1);
-> NULL
mysql>
SELECT NULLIF(1,2);
-> 1
Note that MySQL evaluates
expr1
twice if the arguments are not equal.
12.5. String Functions
Table 12.7. String Operators
Name
Description
ASCII()
[886]
Return numeric value of left-most character
BIN()
[886]
Return a string containing binary representation of a number
BIT_LENGTH()
[886]
Return length of argument in bits
CHAR_LENGTH()
[887]
Return number of characters in argument
CHAR()
[886]
Return the character for each integer passed
CHARACTER_LENGTH()
[887]
A synonym for CHAR_LENGTH()
CONCAT_WS()
[888]
Return concatenate with separator
CONCAT()
[887]
Return concatenated string
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
