MySQL常见字符函数简介

函数名	用途简介
bin()	返回数字的二进制表示
bit_length()	返回字符的字长度，bit
char_length()	返回字符个数
concat()	字符连接函数
concat_ws()	字符连接函数，可以指定分割符，concat的变种
hex()	返回十六进制表示
insert()	在字符串指定位置插入内容
left()	返回字符串左侧指定的部分
length()	返回字符串字节长度
locate()	定位字符位置
lower()	将字符转换为小写，对二进制字符串无效
lpad()	在字符串左侧填充指定字符至指定长度
ltrim()	移除左侧开头的空格
repeat()	将字符串重复指定次数
replace()	将字符替换为指定内容
reverse()	将字符串反转
right()	返回字符串右侧指定的部分，类似left()
rpad()	在字符串右侧填充指定字符至指定长度，类似lpad
rtrim()	移除字符串右侧空格
space()	返回字符串的空格数量
strcmp()	比较两个字符串大小，相等返回0，第一个参数较小返回-1，第一个参数较大返回1
subtring()	返回指定的子串
trim()	移除字符串前后空格
index_substring	按照分隔符返回指定部分

1.bin()

mysql> select bin(12);
+---------+
| bin(12) |
+---------+
| 1100    |
+---------+
1 row in set (0.00 sec)

2.bit_length

mysql> select bit_length('星');
+-------------------+
| bit_length('星')  |
+-------------------+
|                24 |
+-------------------+
1 row in set (0.00 sec)

3.char_length

mysql> select char_length('MySQL');
+----------------------+
| char_length('MySQL') |
+----------------------+
|                    5 |
+----------------------+
1 row in set (0.00 sec)

4.concat
语法：CONCAT(str1,str2,…)
用法：将需要连接的字符用逗号隔开，如果存在null，则返回null。

mysql> select concat('MySQL',' ','is a relational database');
+------------------------------------------------+
| concat('MySQL',' ','is a relational database') |
+------------------------------------------------+
| MySQL is a relational database                 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select concat('MySQL',null);
+----------------------+
| concat('MySQL',null) |
+----------------------+
| NULL                 |
+----------------------+
1 row in set (0.00 sec)

5.concat_ws
语法：CONCAT_WS(separator,str1,str2,…)
用法：concat的变种，separator指定间隔符号，会跳过null。

mysql> select concat_ws(',','MySQL','relational database');
+----------------------------------------------+
| concat_ws(',','MySQL','relational database') |
+----------------------------------------------+
| MySQL,relational database                    |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws(',','MySQL',null,'relational database');  
+---------------------------------------------------+
| concat_ws(',','MySQL',null,'relational database') |
+---------------------------------------------------+
| MySQL,relational database                         |
+---------------------------------------------------+
1 row in set (0.00 sec)

6.insert()
语法：INSERT(str,pos,len,newstr)
用法：对于str字符串，从pos到len的位置，用newstr来替代。如果pos不在str长度范围，则不进行替换；如果len超过str从pos起的剩余长度，则从pos往后的所有部分都替换为newstr；如果任何参数是null，则返回null。

mysql> select insert('MySQL',2,2,'sam'); 
+---------------------------+
| insert('MySQL',2,2,'sam') |
+---------------------------+
| MsamQL                    |
+---------------------------+
1 row in set (0.00 sec)

mysql> select insert('MySQL',-1,2,'sam');
+----------------------------+
| insert('MySQL',-1,2,'sam') |
+----------------------------+
| MySQL                      |
+----------------------------+
1 row in set (0.00 sec)

mysql> select insert('MySQL',-1,null,'sam'); 
+-------------------------------+
| insert('MySQL',-1,null,'sam') |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set (0.00 sec)

7.left()
语法：LEFT(str,len)
用法：返回str左右len长度的部分。

mysql> select left('MySQL',2);
+-----------------+
| left('MySQL',2) |
+-----------------+
| My              |
+-----------------+
1 row in set (0.00 sec)

mysql> select left('MySQL',null);
+--------------------+
| left('MySQL',null) |
+--------------------+
| NULL               |
+--------------------+
1 row in set (0.00 sec)

8.length()

mysql> select length('MySQL');
+-----------------+
| length('MySQL') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

mysql> select length('星');
+---------------+
| length('星')  |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

9.locate()
语法：LOCATE(substr,str), LOCATE(substr,str,pos)
用法：第一个返回substr在str第一次出现的位置，第二个返回str从pos开始，substr第一次出现的位置。如果substr不存在str中，则返回0；如果任何参数是null，则返回null。

mysql> select locate('SQL','MySQLSQL');     
+--------------------------+
| locate('SQL','MySQLSQL') |
+--------------------------+
|                        3 |
+--------------------------+
1 row in set (0.00 sec)

mysql> select locate('SQL','MySQLSQL',5);
+----------------------------+
| locate('SQL','MySQLSQL',5) |
+----------------------------+
|                          6 |
+----------------------------+
1 row in set (0.00 sec)

10.lower()
用法：将字符串转换为小写，需要注意对二进制字符串无效。

mysql> select lower('MySQL');
+----------------+
| lower('MySQL') |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)

mysql> set @str=binary 'MySQL';
Query OK, 0 rows affected (0.00 sec)

mysql> select lower(@str),lower(convert(@str using utf8mb3));
+-------------+------------------------------------+
| lower(@str) | lower(convert(@str using utf8mb3)) |
+-------------+------------------------------------+
| MySQL       | mysql                              |
+-------------+------------------------------------+
1 row in set (0.00 sec)

11.lpad()
语法：LPAD(str,len,padstr)
用法：在str左侧填充padstr至len长度，如果len小于str长度，则将str截断至len长度并返回。

mysql> select lpad('MySQL',7,'a');
+---------------------+
| lpad('MySQL',7,'a') |
+---------------------+
| aaMySQL             |
+---------------------+
1 row in set (0.00 sec)

mysql> select lpad('MySQL',2,'a');
+---------------------+
| lpad('MySQL',2,'a') |
+---------------------+
| My                  |
+---------------------+
1 row in set (0.00 sec)

12.ltrim()

mysql> select ltrim('  MySQL');
+------------------+
| ltrim('  MySQL') |
+------------------+
| MySQL            |
+------------------+
1 row in set (0.00 sec)

13.repeat()

mysql> select repeat('MySQL',2);
+-------------------+
| repeat('MySQL',2) |
+-------------------+
| MySQLMySQL        |
+-------------------+
1 row in set (0.00 sec)

mysql> select repeat('MySQL',0);
+-------------------+
| repeat('MySQL',0) |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

14.repeat()
语法：REPLACE(str,from_str,to_str)
此函数区分大小写。

mysql> select replace('MySQL','M','m');
+--------------------------+
| replace('MySQL','M','m') |
+--------------------------+
| mySQL                    |
+--------------------------+
1 row in set (0.00 sec)

15.substring()
语法：SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len),
SUBSTRING(str FROM pos FOR len)
用法：第一个从pos位置开始提取str的子串，第二个跟第一个相同，第三个从pos开始从str提取长度为len的子串，第四个跟第三个相同。

mysql> select substring('MySQL',2);
+----------------------+
| substring('MySQL',2) |
+----------------------+
| ySQL                 |
+----------------------+
1 row in set (0.00 sec)

mysql> select substring('MySQL',2,2);
+------------------------+
| substring('MySQL',2,2) |
+------------------------+
| yS                     |
+------------------------+
1 row in set (0.00 sec)

mysql> select substring('MySQL',-2,2);
+-------------------------+
| substring('MySQL',-2,2) |
+-------------------------+
| QL                      |
+-------------------------+
1 row in set (0.00 sec)

16.trim()
语法：TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM]
str)
用法：both指字符串两端，leading指字符串左侧，trailing指字符串右侧。

mysql> select trim(leading 'c' from 'cbabc');      
+--------------------------------+
| trim(leading 'c' from 'cbabc') |
+--------------------------------+
| babc                           |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select trim(trailing 'c' from 'cbabc');       
+---------------------------------+
| trim(trailing 'c' from 'cbabc') |
+---------------------------------+
| cbab                            |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select trim(both 'c' from 'cbabc');        
+-----------------------------+
| trim(both 'c' from 'cbabc') |
+-----------------------------+
| bab                         |
+-----------------------------+
1 row in set (0.00 sec)