Working with Strings - MySQL - page 165

the following table, the ^ and $ metacharacters serve much the same purpose as LEFT()
or RIGHT() , at least if you're looking for literal strings:
Pattern match Substring comparison
str REGEXP '^abc' LEFT( str ,3) = 'abc'
str REGEXP 'abc$' RIGHT( str ,3) = 'abc'
For nonliteral patterns, it's typically not possible to construct an equivalent substring
comparison. For example, to match strings that begin with any nonempty sequence of
digits, use this pattern match:
str REGEXP '^[0-9]+'
That is something that LEFT() cannot do (and neither can LIKE , for that matter).
Case sensitivity of a regular-expression match is like that of a string comparison. That
is, it depends on whether the operands are binary or nonbinary strings, and for non‐
binary strings, it depends on their collation. See Recipe 5.7 for discussion of how these
factors apply to comparisons.
A limitation of regular-expression ( REGEXP ) matching compared to
SQL pattern ( LIKE ) matching is that REGEXP works only for single-
byte character sets. Don't expect it to work with multibyte character
sets such as utf8 or sjis .
5.10. Breaking Apart or Combining Strings
Problem
You want to extract a piece of a string or combine strings to form a larger string.
Solution
To obtain a piece of a string, use a substring-extraction function. To combine strings,
use CONCAT() .
Discussion
You can break apart strings 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> SET @date = '2015-07-21';
mysql> SELECT @date, LEFT(@date,4) AS year,
-> MID(@date,6,2) AS month, RIGHT(@date,2) AS day;
+------------+------+-------+------+