学习了数学函数，接着学更为常用的字符串函数。
2，字符串函数
文本处理函数
| 函数 |
说明 |
| CHAR_LENGTH(str) |
返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。 |
| LENGTH(str) |
返回值为字符串的字节长度，使用utf8(UNICODE的一种变长字符编码）编码字符集时，一个汉字是3个字节，一个数字或字母算一个字节 |
| CONCAT(s1,s2…) |
返回结果为连接参数产生的字符串，或许有一个或多个参数。如有任何一个参数为NULL，则返回值为NULL |
| CONCAT_WS(x,s1,s2,…) |
CONCAT With Separator，第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间 |
| INSERT(sl,x,.len,s2) |
返回字符串sl，其子字符串起始于x位置和被字符串s2取代的 len字符。如果x超过字符串长度，则返回值为原始字符串 |
| LOWER(str)、 LCASE(str) |
可以将字符串s如中的字母字符全部转换成小写字母 |
| UPPER(str)、 UCASE(str) |
可以将字符串 str中的字母字符全部转接成大写字母 |
| LEFT(s,n) |
返回字符串s开始的最左边n个字符 |
| LPAD(s1,len,s2) |
返回字符串s1，其左边由字符串s2填补到len字符长度。假如s1的长度大于len，则返回值被缩短至len字符 |
| RPAD(s1,1len,s2) |
返回字符串sl，其右边被字符串s2填补至len字符长度。假如字符串s1的长度大于len，则返回值被缩短到len字符长度 |
| LTRIMK(s) |
返回字符串s。字符串左侧空格字符被删除 |
| RTRIM(s) |
返回字符串s，字符串右侧空格字符被删除 |
| TRIM(s) |
删除字符串s两侧的空格 |
| TRIM(s1 FROM s) |
删除字符串s 中两端所有的子字符串s1,s1为可选项,在未指定情况下，删除空格 |
| REPEAT(s,n) |
返回一个由重复的字符串s组成的字符串，字符串s的数目等于n。若n<=0，则返回一个空字符串。若s或n 为 NULL，则返回NULL |
| SPACE(n) |
返回一个由n个空格组成的字符串 |
| REPLACE(s,s1,s2) |
使用字符串替代字符串s中所有的字符串sl |
| STRCMP(s1,s2) |
若所有的字符串均相同，则返回0；若根据当前分类次序，第一个参数小于第二个，则返回-1，其他情况返回1 |
| SUBSTRING(s,n,len) |
带有len参数的格式，从字符串s返回一个长度同 len字符相同的子字符串，起始于位置n，也可能对n使用一个负值 |
| MID(s,n,len) |
与SUBSTRING(s,n,len)的作用相同 |
| LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str, str1) |
返回子字符串strl在字符串 str中的开始位置 |
| REVERSE(s) |
将字符串s反转，返回的字符串的顺序和s字符串顺序相反 |
| ELT(N,字符串1,字符串2,字符串3....字符串N) |
若N=1，则返回值为字符串1，若N=2，则返回值为字符串2，以此类推 |
| FIELD(s,s1,s2,…) |
返回字符串s在列表s1,s2,...中第一次出现的位置,在找不到s的情况下，返回值为0 |
| FIND_IN_SET(s1,s2) |
返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号‘,’分开的字符串组成的列表 |
| MAKE_SET(x,s1,s2,…) |
返回由x的二进制数指定的相应位的字符串组成的字符串，sl对应比特1，s2对应比特01，以此类推 |
| GROUP_CONCAT() | 函数可以将多行字段数据合并为一行 |
| COALESCE(a,b,c) |
返回字符串中第一个非null的值 |
字符串函数主要用来处理数据库中的字符串数据，MySQL中字符串函数有：计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数、查找指定字符串位置函数等。
1，计算字符串字符数的函数和字符串长度的函数
CHAR_LENGTH(str)返回值为字符串str 所包含的字符个数。一个多字节字符算作一个单字符。
【例26】使用CHAR_LENGTH 函数计算字符串字符个数，输入语句如下：
SELECT CHAR_LENGTH('date'), CHAR_LENGTH('egg'), CHAR_LENGTH('中国');
LENGTH(str)返回值为字符串的字节长度，使用utf8 (UNICODE的一种变长字符编码，又称万国码）编码字符集时，一个汉字是3个字节，一个数字或字母算一个字节。
【例27】使用LENGTH函数计算字符串长度，输入语句如下：
SELECT LENGTH('date'), LENGTH('egg'), LENGTH('中国');
可以看到，计算的结果与CHAR_LENGTH相同，因为英文字符的个数和所占的字节相同，一个字符占一个字节。
2，合并字符串函数CONCAT(s1,s2,...)、CONCAT_WS(x,s1,s2,...)
CONCAT(s1,s2…)返回结果为连接参数产生的字符串，或许有一个或多个参数。如有任何一个参数为NULL，则返回值为NULL。如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。
【例28】使用CONCAT函数连接字符串，输入语句如下：
SELECT CONCAT('My SQL','5.7'), CONCAT('My', NULL, 'SQL'); 
CONCAT ('My SQL', '5.7')返回两个字符串连接后的字符串;
CONCAT('My', NULL, 'SQL')中有一个参数为NULL,因此返回结果为NULL。
CONCAT_WS(x,s1,s2,…)，CONCAT_WS 代表CONCAT With Separator，是CONCAT()的特殊形式，第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其他参数。如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值。
【例29】使用CONCAT_WS函薮连接带分隔符的字符串，输入语句如下t
SELECT CONCAT_WS('-','1st', '2nd', '3rd'), CONCAT_WS('*', '1st', NULL, '3rd');
CONCAT_WS('-', '1st', '2nd"，'3rd')使用分隔符‘-’将3个字符串连接成一个字符串，结果为“1st-2nd-3rd”;
CONCAT_WS('*', '1st', NULL，'3rd')使用分隔符‘*”将两个字符串连接成一个字符串,同时忽略NULL值。
3，替换字符串的函数INSERT(s1,x,len,s2)
INSERT(sl,x,.len,s2)返回字符串sl，其子字符串起始于x位置和被字符串s2取代的 len字符。如果x超过字符串长度，则返回值为原始字符串。假如len的长度大于其他字符串的长度,则从位置x开始替换。若任何一个参数为NULL，则返回值为NULL-
【例30】使用INSERT函薮进行字符串替代操作，输入语句如下：
SELECT INSERT('Quest', 2, 4, 'Wnat') AS col1,
INSERT('Quest', -1, 4, 'What') AS co12,
INSERT('Quest', 3, 100, 'What') AS co13;
第一个函数INSERT('Quest,2,4,"What')将“Quest”第2个字符开始长度为4的字符串替换为What，"结果为“QWhat”。
第二个函数INSERT('Quest', -1, 4, 'what')中起始位置-1超出了字符串长度。直接返回原字符;
第三个函数INSERT('Quest', 3, 100, 'what')替换长度超出了原字符串长度。则从第3个字符开始，截取后面房有的字符，并替换为指定字符What，结果为“QuWhat”
4，字母大小写转换函数
LOWER(str)或者LCASE (str)可以将字符串s如中的字母字符全部转换成小写字母。
【例31】使用LOWER函数或者LCASE函数将字符串中所有字母字符转换为小写，输入语句如下：
SELECT LOWER('BEABTIFUL'), LCASE('We11');
由结果可以看到，原来所有字母为大写的，全部转换为小写。如“BEAUTIFUL”，转换之后为“beautiful”。大小写字母混合的字符串，小写不变，大写字母转换为小写字母，如"We11”，转换之后为“well"。
UPPER(str)或者UCASE(str)可以将字符串 str中的字母字符全部转接成大写字母。
【例 6.32】使用UPPER函数或者UCASE函数将字符串中所有字母字符转换为大写，输入语句如下：
SELECT UPPER('blacK'), UCASE('BLacK');
由结果可以看到，原来所有字母字符为小写的，全部转换为大写，如“balck”，转换之后为“BLACK”，大小写字母混合的字符串，大写不变，小写字母转换为大写字母，如“BLacK”，转换之后为“BLACK”。
5，获取指定长度的字符串的函数LEFT(s,n)和 RIGHT(s,n)
LEFT(s,n)返回字符串s开始的最左边n个字符。
【例33】使用LEFT函数返回字符串中左边的字符,输入语句如下：
SELECT LEFT('football', 5);
函数返回字符串“football”左边开始的长度为5的子字符串，结果为“footb”
RIGHT(s,n)返回字符串中最右边n个字符。
【例34】使用RIGHT函数返回字符串中右边的字符，输入语句如下
SELECT RIGHT('footbal1', 4);
函数返回字符串“football”右边开始的长度为4的子字符串，结果为“ball”。
6，填充字符串的函数LPAD(s1,len,s2)和 RPAD(s1,len,s2)
LPAD(s1,len, s2)返回字符串s1，,其左边由字符串s2填补到len字符长度。假如s1的长度大于len，则返回值被缩短至len字符。
【例35】使用LPAD函数对字符串进行填充操作，输入语句如下：
SELECT LPAD('he11o', 4, '??'), LPAD('hello', 10, '??');
字符串“hello”长度大于4，不需要填充，因此LPADLPAD('he11o',4,'??')只返回被缩短的长度为4的子串“hell ”：字符串“hello”长度小于10，LPAD('hello',10,'??')返回结果为“?????hello”，左侧填充‘?’，长度为10。
RPAD(s1, len,s2)返回字符串sl，其右边被字符串s2填补至len字符长度。假如字符串s1的长度大于len，则返回值被缩短到len字符长度。
【例36】使用RPAD函数对字符串进行填充操作,输入语句如下：
SELECT RPAD('he11o', 4, '?'), RPAD('hello', 10, '?');
字符串“hello”长度大于4，不需要填充，因此RPAD('he11o',4,'?')只返回被缩短的长度为4的子串“ hell ”：字符串“hello”长度小于10，RPAD('hello',10,'?')返回结果为“hello???????2??”，右侧填充“‘?’，长度为10。
7，删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)
LTRIMK(s)返回字符串s。字符串左侧空格字符被删除。
【例37】使用LTRIM函数删除字符串左边的空格，输入语句如下：
SELECT '(book )',CONCAT('(',LTRIM(' book '),')');
LTRIM 只删除字符串左边的空格，而右边的空格不会被删除，“book ”删除左边空格之后的结果为“book”。
RTRIM(s)返回字符串s，字符串右侧空格字符被删除。
【例38】使用RTRIM 函数删除字符串右边的空格，输入语句如下：
SELECT '(book )' , CONCAT('(',RTRIM('book '),')');
RTRIM 只删除字符串右边的空格，左边的空格不会被删除，“book ”删除右边空格之后的结果为“book ”。
TRIM(s)删除字符串s两侧的空格。
【例39】使用TRIM函数删除字符串两侧的空格，使用语句如下：
SELECT '( book )', CONCAT ('(', TRIM(' book '),')');
可以看到，函数执行之后字符串“book”两边的空格都被删除，结果为“book”。
8，删除指定字符串的函数TRIM(s1 FROM s)
TRIM(s1 FROM s)删除字符串s 中两端所有的子字符串s1，s1为可选项,在未指定情况下，删除空格。
【例40】使用TRIM(s1 FROM s)函数删除字符串中两端指定的字符，输入语句如下：
SELECT TRIM('xy' FROM 'xyxboxyokxxyXy');
删除字符串“xyxboxyokxyxy”两端的重复字符串“xy”，而中间的“xy”并不删除，结果为“xboxyokx" .
9，重复生成字符串的函数REPEAT(s,n)
REPEAT(s, n)返回一个由重复的字符串s组成的字符串，字符串s的数目等于n。若n<=0，则返回一个空字符串。若s或n 为 NULL.则返回NULL。
【例41】使用REPEAT函数重复生成相同的字符串，输入语句如下：
SELECT REPEAT('mysq1',3);
REPEAT('MySQL',3)函数返回的字符串由3个重复的“MySQL”字符串组成。
10，空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)
SPACE(n)返回一个由n个空格组成的字符串。
【例42】使用SPAC王函数生成由空格组成的字符串,输入语旬如下：
SELECT CONCAT('(', SPACE(6), ')' );
SPACE(6)返回的字符串由6个空格组成。
REPLACE(s,s1,s2)使用字符串替代字符串s中所有的字符串s1。
【例43】使用REPLACE函数进行字符串替代操作。输入语句如下：
SELECT REPLACE('xxx.mysql.com', 'x', 'w');
REPLACE('xxx.MySQL.com'，"x', "w')将“xxx.rmysqL.com”字符串中的“x’字符替换为'w’字符，结果为“www.mysqLcom”
11，比较字符串大小的函数STRCMP(s1,s2)
STRCMP(s1,s2)若所有的字符串均相同，则返回0;若根据当前分类次序，第一个参数小于第二个，则返回-1，其他情况返回1。
【例44】使用STRCMP函数比较字符串大小,输入语句如下：
SELECT STRCMP('txt', 'txt2'), STRCMP('txt2', 'txt'), STRCMP('txt', 'txt');
“txt”小于“txt2”，因此 STRCMP('txr', 'txt2')返回结果为-1，STRCMP('txt2', 'txt')返回结果为1：“txt”与“txt”相等，因此 STRCMP("txt', "txt')返回结果为0。
12，获取子串的函数SUBSTRING(s,n,len)和 MID(s,n,len)
SUBSTRING(s,n,len)带有len参数的格式，从字符串s返回一个长度同 len字符相同的子字符串，起始于位置n，也可能对n使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的n字符，即倒数第n个字符，而不是字符串的开头位置。
【例45】使用SUBSTRING函数获取指定位置处的子字符串，输入语句如下：
SELECT SUBSTRING('breakfast', 5) AS coll,
SUBSTRING('breakfast', 5,3) AS co12,
SUBSTRING('lunch', -3) AS co13,
SUBSTRING('lunch', -5, 3)AS col4;
SUBSTRING('breakfast',5)返回从第5个位置开始到字符串结尾的子字符串，结果为“kfast”; SUBSTRING(breakfast',5,3)返回从第5个位置开始长度为3的子字符串，结果为“kfa”;
SUBSTRING("unch',-3)返回从结尾开始第3个位置到字符串结尾的子字符串，结果为“nch”; SUBSTRING('lunch',-5,3)返回从结尾开始第5个位置，即字符串开头起，长度为3的子字符串,结果为“lun”。
MID(s,n,len)与 SUBSTRING(s,n,len)的作用相同。
【例46】使用MIDO函数获取指定位置处的子字符串，输入语句如下：
SELECT MID('breakfast', 5) as col1,
MID('breakfast', 5, 3) As col2,
MID('lunch', -3) As co13,
MID('lunch', -5, 3)As col4;
可以看到MID和 SUBSTRING的结果是一样的。
提示： 如果对len使用的是一个小于1的值，则结果始终为空字符串。
13，匹配子串开始位置的函数
LOCATE(str1, str)、POSITION(str1 IN str)和 INSTR(str, str1)3个函数作用相同，返回子字符串strl在字符串 str中的开始位置。
【例47】使用LOCATE，POSITION.INSTR函数查找字符串中指定子字符串的开始位置，输入语句如下：
SELECT LOCATE('ba1l', 'footba1l'), POSITION('ba11' IN 'footba11'),
INSTR('footba11', 'ba11');
子字符串“ball”在字符串“football”中从第5个字母位置开始，因此3个函数返回结果都为5。
14，字符串逆序的函数REVERSE(s)
REVERSE(s)将字符串s反转，返回的字符串的顺序和s字符串顺序相反。
【例48】使用REVERSE函数反转字符串，输入语句如下：
SELECT REVERSE('abc');
可以看到，字符串“abc”经过 REVERSE函数处理之后所有字符串顺序被反转，结果为"cba”。
15，返回指定位置的字符串的函数
ELT(N,字符串1,字符串2,字符串3....字符串N)若N=1，则返回值为字符串1，若N=2，则返回值为字符串2，以此类推。若N小于1或大于参数的数目，则返回值为NULL。
【例49】使用ELT函数返回指定位置字符串，输入语句如下：
SELECT ELT(3, '1st', '2nd', '3rd'), ELT(3, 'net','os');
由结果可以看到，ELT(3,'1st , 2nd ;,"3rd')返回第3个位置的字符串“3rd”；指定返回字符串位置超出参数个数，返回NULL。
16，返回指定字符串位置的函数FIELD(s,s1,s2,…)
FIELD(s,s1,s2,…)返回字符串s在列表s1,s2,...中第一次出现的位置,在找不到s的情况下，返回值为0。如果s为NULL，则返回值为0，原因是NULL不能同任何值进行同等比较。
【例50】使用FIELD函数返回指定字符串第一次出现的位置，输入语句如下：
SELECT FIELD('Hi', 'hihi', 'Bey','Hi', 'bas') AS col1,
FIELD('Bi', 'Hey', 'Lo', 'Hilo', 'foo') AS col12;
FIELD("Hi', hihi', 'Hey', 'Hi"', 'bas')函数中字符串“Hi”出现在列表的第3个字符串位置，因此返回结果为3;
FIELD('Hi', 'Hey', 'Lo', 'Hilo'，'foo')列表中没有字符串“Hi”，因此返回结果为0。
17，返回子串位置的函数FIND_IN_SET(s1,s2)
FIND_IN_SET(s1,s2)返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号‘，’分开的字符串组成的列表。如果s1不在s2或s2为空字符串，则返回值为0。如果任意一个参数为 NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号‘,'时将无法正常运行。
【例51】使用FIND_IN_SETO)函数返回子字符串在字符串列表中的位置,输入语句如下：
SELECT FIND_IN_SET('Hi', 'hihi,Hey,Hi,bas') AS FIND1,
FIND_IN_SET('Hi', 'hihi, Hey, Hi, bas') AS FIND2,
FIND_IN_SET('hi hi', 'hi hi, Hey, Hi, bas') AS FIND3,
FIND_IN_SET('hi,hi', 'hi,hi, Hey, Hi, bas') AS FIND4;
虽然FIND_IN_SET()和FIELD()两个函数格式不同，但作用类似，都可以返回指定字符串在字符串列表中的位置。
18，选取字符串的函数MAKE_SET(x,s1,s2,…)
MAKE_SET(x,s1,s2,…)返回由x的二进制数指定的相应位的字符串组成的字符串，sl对应比特1，s2对应比特01，以此类推。sl，s2...中的NULL值不会被添加到结果中。
【例52】使用MAKE_SET根据二进制位选取指定字符串，输入语句如下：
SELECT MAKE_SET(1, 'a','b','c') as col1,
MAKE_SET(1|4,'hello', 'nice' , 'world') as co12,
MAKE_SET(1|4, 'hello', 'nice',NULL, 'world') as col3,
MAKE_SET(0,'a','b','c')as col4;
1的二进制值为0001，4的二进制值为0100，1与4进行或操作之后的二进制值为0101，从右到左第1位和第3位为1。
MAKE_SET(1, 'a', 'b', 'c')返回第1个字符串；
SET(1|4, 'hello', 'nice', 'world')返回从左端开始第1和第3个字符串组成的字符串；NULL 不会添加到结果中，因此SET(1 | 4, 'hello' 'nice',NULL,"world"')只返回第1个字符串‘hello’;
SET(0, 'a' , 'b' ，'c')返回空字符串。
19，GROUP_CONCAT() 将多行字段数据合并为一行
group_concat()函数可以将多行字段数据合并为一行；且内容之间可以指定分隔符；
group_concat函数的语法如下：
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val]
[LIMIT {[offset,] row_count | row_count OFFSET offset}])创建一个员工表：
CREATE TABLE employee(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(10),
salary DECIMAL(10,2),
dept_id INT DEFAULT 0,
PRIMARY KEY(id)
);插入数据：
insert into employee values
(1,'jack', 7000, 1),
(2, 'tom', 8000, 1),
(3, 'joe', 8000, 1),
(4, 'dell', 9000, 2),
(5, 'ken', 10000, 2),
(6, 'tim', 6000, 3),
(7, 'steven', 7000, 3),
(8, 'tank',9000, 1),
(9, 'tolly', 10000, 1),
(10, 'jony', 12000, 1);1、默认以逗号分隔符连接
语法：
SELECT GROUP_CONCAT(字段名) from 表名;汇总员工姓名：
SELECT GROUP_CONCAT(NAME) FROM employee;执行结果如下：
2、可自定义对字段去重排序和指定分隔符
1、自定义分隔符
语法：
SELECT GROUP_CONCAT(字段名 SEPARATOR '分隔符号') from 表名;汇总员工姓名，用“|”作为分隔符：
SELECT GROUP_CONCAT(NAME SEPARATOR '|') FROM employee;执行结果如下：
2、将整数转为字符串
语法
SELECT GROUP_CONCAT(CAST(字段名 as CHAR) SEPARATOR '分隔符号') from 表名;汇总员工id，用“|”作为分隔符：
SELECT GROUP_CONCAT(CAST(id as CHAR) SEPARATOR '|') FROM employee;执行结果如下：
3、多字段拼接
语法：
SELECT GROUP_CONCAT(字段1,字段2,字段3 SEPARATOR '分隔符号') from 表名;汇总员工id和姓名，用“; ”作为分隔符：
SELECT GROUP_CONCAT(id, name SEPARATOR ' ; ') from employee;执行结果如下：
4、字段去重排序和自定义分隔
语法：
SELECT GROUP_CONCAT(DISTINCT 字段名 ORDER BY 字段名 asc或desc
SEPARATOR '分隔符号') from 表面;往员工表里面插入两个重名的数据：
INSERT INTO employee VALUES
(11,'jack', 9000, 1),
(12, 'tom', 7000, 3);汇总去重的员工姓名，用“|”作为分隔符：
SELECT GROUP_CONCAT(DISTINCT NAME ORDER BY NAME DESC SEPARATOR '|') FROM employee;
执行后结果如下：
5、group_concat的限制和设置
1、查看当前group_concat函数的长度
默认是1024字节即1k;
语法：
SHOW VARIABLES LIKE 'group_concat_max_len';执行结果如下：
2、设置当前Session的group_concat函数的长度，不影响其他的Session
设置当前Session的group_concat的长度为1M
SET SESSION group_concat_max_len = 1048576;执行结果，再查看其长度：
3，设置全局 group_concat函数的长度,需断开后重新连接才生效
设置全局 Session的group_concat的长度为1M
SET GLOBAL group_concat_max_len = 1048576;20，coalesce(a,b,c) 返回第一个非null值
语法：
select coalesce(a,b,c);如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null(没意义)。
这个参数使用的场合为：假如某个字段默认是null，你想其返回的不是null，而是比如0或其他值，可以使用这个函数
SELECT COALESCE('bananna',"pear","apple") AS value1,
COALESCE(NULL,"pear","apple") AS value2,
COALESCE(NULL, NULL,"apple") AS value3;
总结
字符串函数，平时会经常用到。
常用的有
1，合并函数（CONCAT(),CONCAT_WS(x,s1,s2,…)和group_concat()函数) ，
2，大小写（LOWER(str) 小写，UPPER(str) 大写），
3，字符串截取函数SUBSTRING(s,n,len)，
4，子串位置匹配函数（LOCATE(str1, str)、POSITION(str1 IN str)和 INSTR(str, str1)），
5，字符串替换函数REPLACE(s,s1,s2)，
6，子串位置的函数FIND_IN_SET(s1,s2)
上一篇： 《myql 数学函数》
下一篇： 《mysql 日期函数》