To obtain a piece of a string, use a substring-extraction
function. To combine strings, use CONCAT()
.
Strings can be broken apart by using appropriate
substring-extraction functions. For example,
LEFT()
,
MID()
, and RIGHT()
extract substrings from the
left, middle, or right part of a string:
mysql>SELECT name, LEFT(name,2), MID(name,3,1), RIGHT(name,3) FROM metal;
+----------+--------------+---------------+---------------+
| name | LEFT(name,2) | MID(name,3,1) | RIGHT(name,3) |
+----------+--------------+---------------+---------------+
| copper | co | p | per |
| gold | go | l | old |
| iron | ir | o | ron |
| lead | le | a | ead |
| mercury | me | r | ury |
| platinum | pl | a | num |
| silver | si | l | ver |
| tin | ti | n | tin |
+----------+--------------+---------------+---------------+
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>SELECT name, SUBSTRING(name,4), MID(name,4) FROM metal;
+----------+-------------------+-------------+
| name | SUBSTRING(name,4) | MID(name,4) |
+----------+-------------------+-------------+
| copper | per | per |
| gold | d | d |
| iron | n | n |
| lead | d | d |
| mercury | cury | cury |
| platinum | tinum | tinum |
| silver | ver | ver |
| tin | | |
+----------+-------------------+-------------+
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>SELECT name,
->SUBSTRING_INDEX(name,'r',1),
->SUBSTRING_INDEX(name,'i',-1)
->FROM metal;
+----------+-----------------------------+------------------------------+ | name | SUBSTRING_INDEX(name,'r',1) | SUBSTRING_INDEX(name,'i',-1) | +----------+-----------------------------+------------------------------+ | copper | coppe | copper | | gold | gold | gold | | iron | i | ron | | lead | lead | lead | | mercury | me | mercury | | platinum | platinum | num | | silver | silve | lver | | tin | tin | n | +----------+-----------------------------+------------------------------+
Note that if there is no n
-th
occurrence of the character, SUBSTRING_INDEX()
returns the entire
string. SUBSTRING_INDEX()
is
case-sensitive.
Substrings can be used 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 |
| silver |
| tin |
+----------+
To combine strings rather than pull them apart, use the CONCAT()
function. It concatenates all
its arguments and returns the result:
mysql>SELECT CONCAT('Hello, ',USER(),', welcome to MySQL!') AS greeting;
+--------------------------------------------+ | greeting | +--------------------------------------------+ | Hello, cbuser@localhost, welcome to MySQL! | +--------------------------------------------+ mysql>SELECT CONCAT(name,' ends in "d": ',IF(RIGHT(name,1)='d','YES','NO'))
->AS 'ends in "d"?'
->FROM metal;
+--------------------------+ | ends in "d"? | +--------------------------+ | copper ends in "d": NO | | gold ends in "d": YES | | iron ends in "d": NO | | lead ends in "d": YES | | mercury ends in "d": NO | | platinum ends in "d": NO | | silver ends in "d": NO | | tin ends in "d": NO | +--------------------------+
Concatenation can be useful for modifying column values
“in place.” For example, the following
UPDATE
statement
adds a string to the end of each name
value in the metal
table:
mysql>UPDATE metal SET name = CONCAT(name,'ide'),
mysql>SELECT name FROM metal;
+-------------+ | name | +-------------+ | copperide | | goldide | | ironide | | leadide | | mercuryide | | platinumide | | silveride | | tinide | +-------------+
To undo the operation, strip off the last three characters (the
CHAR_LENGTH()
function
returns the length of a string in characters):
mysql>UPDATE metal SET name = LEFT(name,CHAR_LENGTH(name)-3);
mysql>SELECT name FROM metal;
+----------+ | name | +----------+ | copper | | gold | | iron | | lead | | mercury | | platinum | | silver | | tin | +----------+
The concept of modifying a column in place can be applied to
ENUM
or SET
values as well, which usually can be
treated as string values even though they are stored internally as
numbers. For example, to concatenate a SET
element to an existing SET
column, use CONCAT()
to add the new value to the
existing value, preceded by a comma. But remember to account for the
possibility that the existing value might be NULL
. In that case, set the column value
equal to the new element, without the leading comma:
UPDATEtbl_name
SETset_col
= IF(set_col
IS NULL,val
,CONCAT(set_col
,',',val
));
18.218.212.102