4.5 Decomposing or Combining Strings
4.5.1 Problem
You want to break apart a string to
extract a substring or combine strings to form a larger string.
4.5.2 Solution
To obtain a piece of a string, use a substring-extraction function.
To combine strings, use CONCAT( ).
4.5.3 Discussion
Parts of strings can be extracted and displayed. 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.
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 | | |
+----------+-------------------+-------------+
To return everything to the right or left of a given character, use
SUBSTRING_INDEX(str,c,n).
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',2),
-> SUBSTRING_INDEX(name,'i',-1)
-> FROM metal;
+----------+-----------------------------+------------------------------+
| name | SUBSTRING_INDEX(name,'r',2) | SUBSTRING_INDEX(name,'i',-1) |
+----------+-----------------------------+------------------------------+
| copper | copper | copper |
| gold | gold | gold |
| iron | iron | ron |
| lead | lead | lead |
| mercury | mercu | mercury |
| platinum | platinum | num |
| silver | silver | 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 query 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, paul@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
LENGTH( ) function returns the length of a
string):
mysql> UPDATE metal SET name = LEFT(name,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
or the empty string. In that case, set the column value equal to the
new element, without the leading comma:
UPDATE tbl_name
SET set_col = IF(set_col IS NULL OR set_col = '',val,CONCAT(set_col,',',val));
| 
No comments:
Post a Comment