MySQL数据库应用总结—MySQL数据库函数的操作（2）：字符串函数

SQL语法预览：

使用函数：【select函数名(参数1,参数2…);】

详解：

三

字符串函数

字符串函数主要来处理数据库中的字符串数据。MySQL中的字符串函数包括计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数、查找指定字符串位置函数等。

1

计算字符串中字符数的函数和字符串长度函数

CHAR_LENGTH(str)函数的返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。

LENGTH(str)函数的返回值为字符串的字节长度，即使用UTF-8（Unicode的一种变长字符编码，又称为万国码）编码字符集时，一个汉字是3个字节，一个数字或者字母算一个字节，但如果是GBK则是一个汉字2个字节。

案例

使用CHAR_LENGTH()和LENGTH()函数计算字符串字符个数和字节长度，使用的SQL语句如下：

命令语句

select CHAR_LENGTH('hello'), CHAR_LENGTH('MySQL');

select LENGTH('hello'), LENGTH('你好!');

执行结果：

由结果可以看到，中文“你好!”在GBK编码下占5个字节，之所以不是6个是因为我感叹号是英文的感叹号占1个字节。

2

合并字符串函数CONCAT(s1,12,...)、CONCAT_WS(x,s1,s2,...)

CONCAT(s1,s2,...)函数的返回结果为连接参数产生的字符串，或许有一个或多个参数。如果有任何一个参数为null，则返回值为null。如果所有参数均为非二进制字符串，则结果为非二进制字符串。如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。

CONCAT_WS(x,s1,s2,...)函数中CONCAT_WS代表CONCAT With Separator，是CONCAT()的特殊形式。第一个参数x是其他参数的分隔符，分隔符的位置要放在两个连接的字符串之间。分隔符可以是一个字符串，也可以是其他参数。如果分割符为null，则结果为null。函数会忽略分隔符参数之后的null值。

案例

使用CONCAT()函数连接字符串，使用CONCAT_WS()函数连接带分隔符的字符串，SQL语句如下：

命令语句

select CONCAT('My','SQL'), CONCAT('Oracle',null,'MySQL');

select CONCAT_WS('-','UTF','8'), CONCAT_WS('_','CONCAT',null,'WS');

执行结果：

CONCAT_WS('_','CONCAT',null,'WS')用“_”连接，同时忽略了null值。

3

替换字符串的函数INSERT(s1,xlen,s2)

INSERT(s1,x,len,s2)函数将字符串s1中的x位置开始长度为len的字符串用s2替换。

案例

使用INSERT()函数进行字符串替换操作，SQL语句如下：

命令语句

select INSERT('MySQL',3,8,'Oracle') as col1,INSERT('MySQL',-2,8,'Oracle') as col2, INSERT('MySQL',6,20,'Oracle') as col3;

执行结果：

第一列将MySQL从第三个字符开始到第八个字符结束全部替换成Oracle，结果为MyOracle；第二列中起始位置-2超出了字符串MySQL的长度范围，直接返回原字符MySQL；第三列的替换长度20超过了原字符串Oracle的长度，则从第6个字符开始，截取后面所有字符，替换为指定字符Oracle，结果为MySQLOracle。

4

字母大小写转换函数LOWER()、LCASE()和UPPER()、UCASE()

LOWER(str)函数或这LCASE(str)函数可以将字符串str中的字母字符全部转换为小写字母；

UPPER(str)或者UCASE(str)函数可以将字符串str中的字母字符全部转换成大写字母。

案例

使用LOWER()函数或者LCASE()函数将字符串中的所有字母字符转换为小写形式；使用UPPER()函数或者UCASE()函数将字符串中所有的字母字符转换为大写形式，SQL语句如下：

命令语句

select LOWER('SYNCHRONIZED'), LCASE('DEPRECATED');

select UPPER('max_value'), UCASE('purple');

执行结果：

5

获取指定长度字符串的函数LEFT(s,n)和RIGHT(s,n)

LEFT(s,n)函数返回字符串s最左边的n个字符；RIGHT(s,n)函数返回字符串最右边的字符。

案例

使用LEFT()函数返回字符串左边的字符，使用RIGHT()函数返回字符串右边的字符，SQL语句如下：

命令语句

select LEFT('transient',5), LEFT('serializable',5), LEFT('PrintStream',5);

select RIGHT('BinaryTree',4), RIGHT('Runnable',4), RIGHT('ListIterator',8);

可以看到，LIFT()返回最左边的字符，从左向右数；RIGHT()返回最右边的字符，从右往左数。

6

填充字符串的函数LPAD(s1,len,s2)和RPAD(s1,len,s2)

LPAD(s1,len,s2)函数返回字符串s1，其左边由字符串s2填补到len字符长度。假如s1的长度大于len，则返回值被缩短至len字符长度；

RPAD(s1,len,s2)函数返回字符串s1，其右边被字符串s2填补至len字符长度，假如字符串s1的长度大于len，则返回值被缩短至len字符长度。

案例

使用LPAD()函数和RPAD()函数对字符串进行填充操作，SQL语句如下：

命令语句

select LPAD('MySQL',2,'?'), LPAD('MySQL',8,'?');

select RPAD('MySQL',2,'?'), RPAD('MySQL',8,'?');

执行结果：

7

删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)

LTRIM(s)函数返回紫都城s，字符串左侧的空格被删除；

RTRIM(s)返回字符串s，字符串右侧的空格被删除；

TRIM(s)函数用来删除字符串两侧的空格。

案例

使用LTRIM()、RTRIM()和TRIM()函数删除字符串空格，SQL语句如下：

命令语句

select '( seali521 )',CONCAT('(',LTRIM(' seali521 '),')');

select '( seali521 )',CONCAT('(',RTRIM(' seali521 '),')');

select '( seali521 )',CONCAT('(',TRIM(' seali521 '),')');

执行结果：

8

删除指定字符串的函数TRIM(s1,FROM s)

TRIM(s1 FROM s)函数可以删除字符串s中两端所有的字符串s1。s1为可选项，在未指定的情况下，删除空格。

案例

使用TRIM(s1 FROM s)函数删除字符串中两端指定的字符，SQL语法如下：

命令语句

select TRIM('ab' FROM 'aboaobabo'),TRIM('a' from 'alibaba'), TRIM('一' from '一二一'), TRIM('牛' from '牛什么牛'), TRIM('Do not give me your ' from 'Do not give me your shit');

执行结果：

可以看到TRIM(s1 FROM s)函数是删除两端相同的字符串。

9

重复生成字符串的函数REPEAT(s,n)

REPEAT(s,n)函数返回一个由重复的字符串s组成字符串，字符串s的数目等于n。若n

案例

使用REPEAT()函数重复生成相同字符串，SQL语法如下：

命令语句

select REPEAT('重要的事情…',3), REPEAT('synchronized ',2));

执行结果：

10

空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)

SPACE(n)函数返回一个由n个空格组成的字符串；

REPLACE(s,s1,s2)函数使用字符串s2替代字符串s中所有字符串s1。

案例

使用SPACE()函数生成由空格组成的字符串，使用REPLACE()函数进行字符串替换操作，SQL语句如下：

命令语句

select CONCAT('过路人等',SPACE(2),'不得在此大小便!'), CONCAT('过路人', SPACE(2),'等不得',SPACE(2),'在此大小便!');

select REPLACE('来两个人，我要搬花','搬','班'), REPLACE('水房的确有个女的在洗枣','洗枣','洗澡');

执行结果：

可以看到，用空格函数断句“过路人等不得在此大小便！”将条幅“过路人等，不得在此大小便！”变成“过路人，等不得，在此大小便！”哈哈…；用替换函数将老师话“来两个人，我要搬花”变成“来两个人，我要班花”，将“水房的确有个女的在洗枣”变成“水房的确有个女的在洗澡”，笑话百出。

11

比较字符串大小的函数STRCMP(s1,s2)

STRCMP(s1,s2)函数中的字符串若相同，则返回0；若s1小于s2，则返回-1，否则返回1。

案例

使用STRCMP()函数比较字符串大小，SQL语句如下：

命令语句

select STRCMP('我的长！','我比你长!'), STRCMP('我颜值高!','我心灵美!'), STRCMP('中美贸易战!','美中贸易战!');

执行结果：

可以看出，比较方法是将所有文字按照统一编码的十进制逐个字符进行大小比较，只要有一个字符大结果就大。看看图中“我颜值很高?”和“我颜值很高!!!”的比较结果是1，说明前者更大，因为?号的ASCII值大于!号的ASCII，所以就得出结论了。并与字符长短无关！

12

获取字符串的函数SUBSTRING(s,n,len)和MID(s,n,len)

SUBSTRING(s,n,len)函数表示从字符串s中返回一个长度为len的子字符串，起始位置为n。若n为负值，则子字符串起始于字符串结尾的n字符，即倒数第n个字符，而不是字符串的开头位置；

MID(s,n,len)函数与SUBSTRING(s,n,len)函数的作用相同。

案例

使用SUBSTRING()和MID()函数获取指定位置处的子字符串，SQL语句如下：

命令语句

select SUBSTRING('sbend',1,2), SUBSTRING('synchronized',7), SUBSTRING('synchronized',-5), SUBSTRING('synchronized',-9,2);

select MID('sbend',1,2), MID('synchronized',7), MID('synchronized',-5), MID('synchronized',-9,2);

执行结果：

可以看到函数MID()与SUBSTRING()的结果是相同。

13

匹配子字符串开始位置函数

LOCATE(str1,str)、POSITION(str1 IN str)和INSTR(str,str1)三个函数的作用相同，返回子字符串str1在字符串str中的开始位置。

案例

使用LOCATE()、POSITION()、INSTR()函数查找字符串中指定子字符串的开始位置，SQL语句如下：

命令语句

select LOCATE('ch','syrchronized'), LOCATE('r','syrchronized'),LOCATE (null,'syrchronized');

select POSITION('ch' IN 'syrchronized'), INSTR('syrchronized','nized');

执行结果：

由结果可以看到，函数LOCATE()、POSITION()和INSTR()执行结果相同。

14

获取字符串的函数SUBSTRING(s,n,len)和MID(s,n,len)

SUBSTRING(s,n,len)函数表示从字符串s中返回一个长度为len的子字符串，起始位置为n。若n为负值，则子字符串起始于字符串结尾的n字符，即倒数第n个字符，而不是字符串的开头位置；

MID(s,n,len)函数与SUBSTRING(s,n,len)函数的作用相同。

案例

使用SUBSTRING()和MID()函数获取指定位置处的子字符串，SQL语句如下：

命令语句

select SUBSTRING('sbend',1,2), SUBSTRING('synchronized',7), SUBSTRING('synchronized',-5), SUBSTRING('synchronized',-9,2);

select MID('sbend',1,2), MID('synchronized',7), MID('synchronized',-5), MID('synchronized',-9,2);

执行结果：

15

返回指定位置的字符串的函数ELT(n,str1,str2,...,strn)

ELT(n,str1,str2,...,strn)函数若n=1，则返回值为str1；若n=2，则返回值为str2；以此类推。若n小于或大于参数的数目，则返回值为null。

案例

使用ELT()函数返回指定位置的字符串，SQL语句如下：

命令语句

select ELT(2,'str1','str2','str3','strn'),ELT(10,'str1','str2','strn'),ELT(-1,'str1','str2','strn');

执行结果：

16

返回指定字符串位置的函数FIELD(s,s1,s2,...)

FIELD(s,s1,s2,...)函数返回字符串s在列表s1,s2,...中第一次出现的位置，在找不到s的情况下，返回值为0。如果s为null，则返回值为0，原因是null不能同任何值进行比较。

案例

使用FIELD()函数返回指定字符串第一次出现的位置，SQL语句如下：

命令语句

select FIELD('str3','str1','str2','str3','strn') as col1, FIELD('str1','str1','str2', 'strn') as col2, FIELD('str','str1','str2','strn') as col3;

执行结果：

17

返回子字符串位置的函数FIND_IN_SET(s1,s2)

FIND_IN_SET(s1,s2)函数返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由都好分开的字符串组成的列表。如果s1不再s2中或s2为null字符串，则返回值为0。如果任意一个参数为null，则返回值为null。若在第一个参数s1包含逗号，则无法正常运行。

案例

使用FIND_IN_SET()函数返回子字符串在字符串列表中的位置，SQL语句如下：

命令语句

select FIND_IN_SET('str3','str1,str2,str3,strn'), FIND_IN_SET ('str','str1,str2,strn');

执行结果：

18

选取字符串的函数MAKE_SET(x,s1,s2,...)

MAKE_SET(x,s1,s2,...)函数按x的二进制从s1,s2,...sn中选取字符串。例如13的二进制是1101.这个二进制从右往左的第1位，第3位和第4位是1，所以选取s1,s3和s4。

案例

使用MAKE_SET()函数根据二进制选取指定字符串，SQL语句如下：

命令语句

select MAKE_SET(1,'W','T','O') as col1, MAKE_SET(1|4,'Q','W','E','R') as col2, MAKE_SET(1|4,'My','book','SQL','OK') as col3,MAKE_SET(0,'a1','a2','a3','a4') as col4;

执行结果：

字符串函数介绍完毕，下期介绍日期时间函数。

未完，下期待续...

SQL语法总结：

使用函数：【select函数名(参数1,参数2…);】

end

欢迎关注互动｜未来科技008

微信ID：seali_521

欢迎关注互动｜十年之前diary

微信ID：Lseali_521

十年之前diary

文学随笔，生活点滴

广读胸中有本，勤写笔下生辉