MySQL常用函数汇总——字符串操作函数

mysql> SELECT CONCAT(‘MySQL‘,‘5.7‘),CONCAT(‘MySQL‘,NULL);
+-----------------------+----------------------+
| CONCAT(‘MySQL‘,‘5.7‘) | CONCAT(‘MySQL‘,NULL) |
+-----------------------+----------------------+
| MySQL5.7              | NULL                 |
+-----------------------+----------------------+
1 row in set (0.03 sec)

mysql> SELECT INSERT(‘Football‘,2,4,‘Play‘) AS col1,
    -> INSERT(‘Football‘,-1,4,‘Play‘) AS col2,
    -> INSERT(‘Football‘,3,20,‘Play‘) AS col3;
+----------+----------+--------+
| col1     | col2     | col3   |
+----------+----------+--------+
| FPlayall | Football | FoPlay |
+----------+----------+--------+
1 row in set (0.04 sec)

mysql> SELECT LOWER(‘BLUE‘),LOWER(‘Blue‘);
+---------------+---------------+
| LOWER(‘BLUE‘) | LOWER(‘Blue‘) |
+---------------+---------------+
| blue          | blue          |
+---------------+---------------+
1 row in set (0.03 sec)

mysql> SELECT UPPER(‘green‘),UPPER(‘Green‘);
+----------------+----------------+
| UPPER(‘green‘) | UPPER(‘Green‘) |
+----------------+----------------+
| GREEN          | GREEN          |
+----------------+----------------+
1 row in set (0.03 sec)

mysql> SELECT LEFT(‘MySQL‘,2);
+-----------------+
| LEFT(‘MySQL‘,2) |
+-----------------+
| My              |
+-----------------+
1 row in set (0.04 sec)

mysql> SELECT RIGHT(‘MySQL‘,3);
+------------------+
| RIGHT(‘MySQL‘,3) |
+------------------+
| SQL              |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT ‘[   mobile   ]‘,CONCAT(‘[‘,TRIM(‘   mobile   ‘),‘]‘);
+----------------+--------------------------------------+
| [   mobile   ] | CONCAT(‘[‘,TRIM(‘   mobile   ‘),‘]‘) |
+----------------+--------------------------------------+
| [   mobile   ] | [mobile]                             |
+----------------+--------------------------------------+
1 row in set (0.07 sec)

mysql> SELECT REPLACE(‘aaa.mysql.com‘,‘a‘,‘w‘);
+----------------------------------+
| REPLACE(‘aaa.mysql.com‘,‘a‘,‘w‘) |
+----------------------------------+
| www.mysql.com                    |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBSTRING(‘computer‘,3) AS col1,
    -> SUBSTRING(‘computer‘,3,4) AS col2,
    -> SUBSTRING(‘computer‘,-3) AS col3,
    -> SUBSTRING(‘computer‘,-5,3) AS col4;
+--------+------+------+------+
| col1   | col2 | col3 | col4 |
+--------+------+------+------+
| mputer | mput | ter  | put  |
+--------+------+------+------+
1 row in set (0.00 sec)

mysql> SELECT REVERSE(‘hello‘);
+------------------+
| REVERSE(‘hello‘) |
+------------------+
| olleh            |
+------------------+
1 row in set (0.00 sec)