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 |
+----------+
Search WWH ::




Custom Search