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

 

1. LENGTH 计算字符串长度函数，返回字符串的字节长度

   mysql> SELECT LENGTH('name'),LENGTH('数据库');
   +----------------+---------------------+
   |LENGTH('name')  | LENGTH('数据库')    |
   +----------------+---------------------+
   |              4 |                   9 |
   +----------------+---------------------+
   1 row in set (0.04 sec)

    

2. CONCAT 合并字符串函数，返回结果为连接参数产生的字符串，可以有多个参数

   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)

    

3. INSERT 替换字符串

   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)

    

4. LOWER 字母转换为小写

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

    

5. UPPER 字母转换为大写

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

    

6. LEFT 从左侧字截取符串，返回字符串左边的若干个字符

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

    

7. RIGHT 从右侧字截取符串，返回字符串右边的若干个字符

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

    

8. TRIM 删除字符串左右两侧的空格

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

    

9. REPLACE 替换字符串

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

    

10. SUBSTRING 截取字符串

    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)

     

11. REVERSE 反转字符串

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