1. 计算字符串字符数和字符串长度 - char_length(s)
char_length(str): 返回str所包含的字符个数。
mysql> select char_length('mysql');
+----------------------+
| char_length('mysql') |
+----------------------+
| 5 |
+----------------------+
2. 合并字符 - concat(s1,s2,...) 与 concat_ws(x,s1,s2,...)
concat(s1,s2,...):回结果为连接参数产生的字符串，或许有一个或者多个参数。如果有任何一个返回值为null, 则返回值为null.
mysql> select concat('mysql',' ','5.5',' ',null,'function');
+-----------------------------------------------+
| concat('mysql',' ','5.5',' ',null,'function') |
+-----------------------------------------------+
| null |
+-----------------------------------------------+
concat_ws(x,s1,s2,...): 代表concat with separator, 是concat的特殊形式。第一个参数ｘ是其它参数的分隔符，分隔符的位置在要连接的字符串之间。分隔符可以是一个字符串，也可是其他参数。如果分隔符为null,则结果为null。
mysql> select concat_ws('.','david','tian'), concat_ws(null,'mysql','5.5');
+-------------------------------+-------------------------------+
| concat_ws('.','david','tian') | concat_ws(null,'mysql','5.5') |
+-------------------------------+-------------------------------+
| david.tian | null |
+-------------------------------+-------------------------------+
3. 替换字符串函数 - insert(s1, x, len, s2)
insert(s1,x,len,s2):返回字符串s1, 其子字符串起始于x位置和被字符串s2取代的len字符。如果x超过字符串长度，则返回值为原始字符串。假如len的长度大于其它字符串的长度，则从位置x开始替换。若任何一个参数为null, 则返回值为null。
mysql> select insert('softtekian',2,4,'!@#$') as c1,
-> insert('softtekian',-1,4,'@@@@') as c2,
-> insert('softtekian',3,100,'$$') as c3,
-> insert('softtekian',2,4,'%@') as c4;
+------------+------------+------+----------+
| c1 | c2 | c3 | c4 |
+------------+------------+------+----------+
| s!@#$ekian | softtekian | so$$ | s%@ekian |
+------------+------------+------+----------+
4. 字母大小写转换函数- lower(s), lcase(s), upper(s), ucase(s)
lower(str)和lcase(str):将字符串str中的字母全部转换成小写字母。
mysql> select lower('mysql and oracle asm') as c1, lcase('database administrator') as c2;
+----------------------+------------------------+
| c1 | c2 |
+----------------------+------------------------+
| mysql and oracle asm | database administrator |
+----------------------+------------------------+
upper(str)和ucase(str):可以将字符串str中的字母全部转换成大写字母。
mysql> select upper('sunshine.ma') c1,ucase('sunshine.ma') c2;
+-------------+-------------+
| c1 | c2 |
+-------------+-------------+
| sunshine.ma | sunshine.ma |
+-------------+-------------+
5. 获取指定长度字符串：left(s,n), right(s,n)
left(s,n): 返回字符串s开始最左边n个字符。
mysql> select left('this is a testing email',7) as c1;
+---------+
| c1 |
+---------+
| this is |
+---------+
right(s,n):返回字符串str最右边n个字符。
mysql> select right('this is a testing email',7) as c1;
+---------+
| c1 |
+---------+
| g email |
+---------+
6. 填充字符串函数：lpad(s1, len, s2), rpad(s1, len, s2)
lpad(s1, len, s2): 返回字符串s1，其左边由字符串s2填补到len字符长度。假如s1的长度大于len, 则返回值缩短至len字符。
mysql> select lpad('hello',4,'%%') as c1, lpad('hello',10,'*') as c2;
+------+------------+
| c1 | c2 |
+------+------------+
| hell | *****hello |
+------+------------+
rpad(s1, len, s2): 返回字符串s1, 其右边被字符串s2填补至len字符串s1的长度大于len, 则返值被缩短到len字符长度。
mysql> select rpad('hello',4,'%') as c1, rpad('hello',10,'*') as c2;
+------+------------+
| c1 | c2 |
+------+------------+
| hell | hello***** |
+------+------------+
7. 删除空格字符串函数：ltrim(s), rtrim(s), trim(s)
ltrim(s): 返回字符串s,字符串左侧空格字符被删除。
mysql> select ltrim(' book ') as c1;
+---------+
| c1 |
+---------+
| book |
+---------+
rtrim(s): 返回字符串s,字符串右侧空格字符被删除。
mysql> select rtrim(' book ') as c1;
+---------+
| c1 |
+---------+
| book |
+---------+
trim(s): 返回字符串s,字符串两侧空格字符被删除。
mysql> select trim(' book ') as c1;
+------+
| c1 |
+------+
| book |
+------+
8. 删除指定字符串的函数：trim(s1 from s)
trim(s1 from s): 删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定情况下，删除空格。
mysql> select trim(' boook ') as c1, trim('xy' from 'xyxydxydxyxy') as c2;
+-------+------+
| c1 | c2 |
+-------+------+
| boook | dxyd |
+-------+------+
9. 重复生成字符串的函数：repeat(s,n)
repeat(s,n): 返回一个由重复的字符串s组成的字符串，字符串s的数目等于n。若n
mysql> select repeat('abc',3) as c1, repeat('abc',-1) as c2, repeat('abc',null) as c3;
+-----------+------+------+
| c1 | c2 | c3 |
+-----------+------+------+
| abcabcabc | | null |
+-----------+------+------+
10. 空格函数：space(n)
space(n):返回一个由n个空格组成的字符串。
mysql> select concat('(',space(6),')') as c1, char_length(space(6)) as c2;
+----------+----+
| c1 | c2 |
+----------+----+
| ( ) | 6 |
11. 替换函数：replace(s,s1,s2)
replace(s,s1,s2):使用字符串s2替代字符串s中所有的字符串s1。
mysql> select replace('xxx.mysql.com','x','w') as c1;
+---------------+
| c1 |
+---------------+
| www.mysql.com |
12. 比较字符串大小函数：strcmp(s1,s2)
strcmp(s1,s2):若所有的字符串均相同，则返回0；若根据当前分类次序，第一个参数小于第二个，则返回-1,其它情况返回1。
mysql> select strcmp('txt','txta') as c1, strcmp('txta','txt') as c2, strcmp('txt','txt') as c3;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| -1 | 1 | 0 |
+----+----+----+
13. 字符串截取函数：substring(s,n,len), mid(s,n,len)
substring(s,n,len):从字符串s返回一个长度为len的子字符串，起始位置为n。若n为负数，则子字符串的位置起始于字符串结尾的n个字符，即倒数第n个字符。若len省略，则取至结尾。
mysql> select substring('breaskfast',5) as c1,
-> substring('breaskfast',5,3) as c2,
-> substring('breakfast',-3) as c3,
-> substring('breakfast',-5,3) as c4;
+--------+-----+-----+-----+
| c1 | c2 | c3 | c4 |
+--------+-----+-----+-----+
| skfast | skf | ast | kfa |
+--------+-----+-----+-----+
mid(s,n,len): 与substring(s,n,len)作用相同。
mysql> select mid('breaskfast',5) as c1, mid('breaskfast',5,3) as c2, mid('breakfast',-3) as c3, mid('breakfast',-5,3) as c4;+--------+-----+-----+-----+
| c1 | c2 | c3 | c4 |
+--------+-----+-----+-----+
| skfast | skf | ast | kfa |
+--------+-----+-----+-----+
14. 匹配子串开始位置函数：locate(s1,s2), position(s1 in s2), instr(s2,s1)
locate(s1,s2): 返回子字符串s1在字符串s2中的开始位置。
position(s1 in s2): 返回子字符串s1在字符串s2中的开始位置。
instr(s2,s1):返回子字符串s1在字符串s2中的开始位置。
mysql> select locate('ball','football') c1,
-> position('ball' in 'football') c2,
-> instr('football','ball') c3;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| 5 | 5 | 5 |
+----+----+----+
15. 字符串逆序函数：reverse(s)
reverse(s): 将字符串s反转，返回的字符串的顺序和s字符串顺序相反。
mysql> select reverse('i love you') as c1;
+------------+
| c1 |
+------------+
| uoy evol i |
+------------+
16. 返回指定位置的字符串函数：elt(n,s1,s2,s3,...,sn)
elt(n,s1,s2,s3,...,sn): 若n=1，则返回字符串s1，若n=2,则返回字符串s2,依此类推。若n小于1或大于参数的数目，则返回值为null。
mysql> select elt(3,'1st','2nd','3rd') as c1, elt(3,'oracle','mysql') as c2;
+------+------+
| c1 | c2 |
+------+------+
| 3rd | null |
+------+------+
17. 返回指定字符串位置的函数：field(s,s1,s2,...)
field(s,s1,s2,...):返回字符串s在列表s1,s2,...中第一次出现的位置，在找不到s的情况下，返回值为0。如果s为null，则返回值为0，原因是null不能同任何值进行同等比较。
mysql> select field('hi','hihi','hey','hi','bas','ciao') as c1, field('hi','hey','lo','hilo','foo') as c2;
+----+----+
| c1 | c2 |
+----+----+
| 3 | 0 |
+----+----+
18. 返回子串位置的函数：find_in_set(s1,s2)
find_in_set(s1,s2): 返回字符串s1在字符串s2中出现的位置，字符串列表是一个由多个逗号“,”分开的字符串组成的列表。如果s1不在s2中或s2为空字符串，则返回0。如果任何一个参数为null，则返回值为null。s1中不能包含一个逗号“,”。
mysql> select find_in_set('hi','hihi,hey,hi,bas') as c1;
+----+
| c1 |
+----+
| 3 |
+----+
19. 选取字符串的函数：make_set(x,s1,s2,...)
make_set(x,s1,s2,...): 返回由x的二进制数指定的相应位的字符串组成的字符串，s1对应比特1,s2对应比特01,依此类推。s1,s2...中的null值不会被添加到结果中。
mysql> select make_set(1,'a','b','c') as c1, make_set(1|4,'hello','nice','world') as c2, make_set(1|4,'hello','nice',null,'world') as c3, make_set(0,'a','b','c') as c4;
+----+-------------+-------+----+
| c1 | c2 | c3 | c4 |
+----+-------------+-------+----+
| a | hello,world | hello | |
+----+-------------+-------+----+
说明：
1的二进制值为0001, 4的二进制值为0100，1和4进行或操作之后的二进制值为0101，从右到左第1位和第3位为１。
make_set(1,’a’,’b’,’c’): 返回第1个字符串；
make_set(1|4,'hello','nice','world')：返回从左端开始第1和第3个字符组成的字符串；
make_set(1|4,'hello','nice',null,'world')：null值不会添加到结果中，因此只会返回第一个字符串；
make_set(0,'a','b','c'): 返回空字符串。
如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方，请给予指正，非常感谢！
联系方式：david.louis.tian@outlook.com
版权＠：转载请标明出处，否则追究法律责任，后果自负！