Home >Database >Mysql Tutorial >MySQL函数学习笔记二:字符函数_MySQL

MySQL函数学习笔记二:字符函数_MySQL

WBOY
WBOYOriginal
2016-06-01 13:02:30816browse

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的特殊形式。第一个参数X是其它参数的分隔符,分隔符的位置在要连接的字符串之间。分隔符可以是一个字符串,也可是其他参数。如果分隔符为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位为1。

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

版权@:转载请标明出处,否则追究法律责任,后果自负!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn