Database Reference
In-Depth Information
| @date | year | month | day |
+------------+------+-------+------+
| 2015-07-21 | 2015 | 07 | 21 |
+------------+------+-------+------+
For
LEFT()
and
RIGHT()
, the second argument indicates how many characters to return
from the left or right end of the string. For
MID()
, the second argument is the starting
position of the substring you want (beginning from 1), and the third argument indicates
how many characters to return.
The
SUBSTRING()
function takes a string and a starting position, returning everything
to the right of the position.
MID()
acts the same way if you omit its third argument
because
MID()
is actually a synonym for
SUBSTRING()
:
mysql>
SET @date = '2015-07-21';
mysql>
SELECT @date, SUBSTRING(@date,6), MID(@date,6);
+------------+--------------------+--------------+
| @date | SUBSTRING(@date,6) | MID(@date,6) |
+------------+--------------------+--------------+
| 2015-07-21 | 07-21 | 07-21 |
+------------+--------------------+--------------+
Use
SUBSTRING_INDEX(
str
,
c
,
n
)
to return everything to the right or left of a given
character. It searches into a string
str
for the
n
-th occurrence of the character
c
and
returns everything to its left. If
n
is negative, the search for
c
starts from the right and
returns everything to the right of the character:
mysql>
SET @email = 'postmaster@example.com';
mysql>
SELECT @email,
->
SUBSTRING_INDEX(@email,'@',1) AS user,
->
SUBSTRING_INDEX(@email,'@',-1) AS host;
+------------------------+------------+-------------+
| @email | user | host |
+------------------------+------------+-------------+
| postmaster@example.com | postmaster | example.com |
+------------------------+------------+-------------+
If there is no
n
-th occurrence of the character,
SUBSTRING_INDEX()
returns the entire
string.
SUBSTRING_INDEX()
is case sensitive.
You can use substrings for purposes other than display, such as to perform comparisons.
The following statement finds metal names having a first letter that lies in the last half
of the alphabet:
mysql>
SELECT name from metal WHERE LEFT(name,1) >= 'n';
+----------+
| name |
+----------+
| platinum |
| tin |
+----------+