最新在看关于mysql书籍,看到了关于mysql函数这一章,感觉东西很多,于是总结下,方便以后的查询.
1.字符串函数
1)concat(s1,s2,...sn)函数:把传入的参数连接成一个字符串,任何字符串与null进行连接
的结果都是null.
eg: mysql>select concat(‘aaa’,'bbb','ccc'),concat(‘aaa’,null);
+---------------------------+--------------------+
| concat('aaa','bbb','ccc') | concat('aaa',null) |
+---------------------------+--------------------+
| aaabbbccc | NULL |
+---------------------------+--------------------+
2)insert(str,x,y,instr)函数:将字符串str从第X位置开始,Y个字符长的字串替换成字符串instr.
eg: mysql> select insert ('beijing2008you',12,3,'me');
+-------------------------------------+
| insert ('beijing2008you',12,3,'me') |
+-------------------------------------+
| beijing2008me |
+-------------------------------------+
3)lower(str)和upper(str)函数:把字符串转化成小写或者大写.
eg: mysql> select lower('BEIJING2008'),upper('beijing2008');
+----------------------+----------------------+
| lower('BEIJING2008') | upper('beijing2008') |
+----------------------+----------------------+
| beijing2008 | BEIJING2008 |
+----------------------+----------------------+
4)left(str,x)和right(str,x)函数:分别返回字符串最左边的x个字符和最右边的x个字符,如果第二个
参数是null,那么将不返回任何字符串.
eg: mysql> select left('beijing2008',7),left('beijing',null),right
('beijing2008',4);
+-----------------------+----------------------+------------------------+
| left('beijing2008',7) | left('beijing',null) | right('beijing2008',4) |
+-----------------------+----------------------+------------------------+
| beijing | NULL | 2008 |
+-----------------------+----------------------+------------------------+
5)lpad(str,n,pad)和rpad(str,n,pad)函数:用字符串pad对str最左边和最右边进行填充,直到长度为
n个字符长度.
eg: mysql> select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');
+---------------------------+---------------------------+
| lpad('2008',20,'beijing') | rpad('beijing',20,'2008') |
+---------------------------+---------------------------+
| beijingbeijingbe2008 | beijing2008200820082 |
+---------------------------+---------------------------+
6)ltrim(str)和rtrim(str)函数:去掉字符串str左侧和右侧空格.
eg: mysql> select ltrim(' |beijing'),rtrim('beijing| ');
+--------------------+--------------------+
| ltrim(' |beijing') | rtrim('beijing| ') |
+--------------------+--------------------+
| |beijing | beijing| |
+--------------------+--------------------+
7)repeat(str,x)函数:返回str重复x次的结果.
eg: mysql> select repeat('mysql ',3);
+--------------------+
| repeat('mysql ',3) |
+--------------------+
| mysql mysql mysql |
+--------------------+
8)replace(str,a,b)函数:用字符串b替换字符串str中所有出现的字符串a.
eg: mysql> select replace('beijing_2010','_2010','2008');
+----------------------------------------+
| replace('beijing_2010','_2010','2008') |
+----------------------------------------+
| beijing2008 |
+----------------------------------------+
9)strcmp(s1,s2)函数:比较字符串s1和s2的ASCLL码值的大小.s1<s2,返回-1;s1=s2,返回0;
s1>s2,返回1.
eg: mysql> select strcmp('a','b'),strcmp('b','b'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('b','b') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
| -1 | 0 | 1 |
+-----------------+-----------------+-----------------+
10)trim(str)函数:去掉目标字符串的开头和结尾的空格.
eg: mysql> select trim(' $ beijing2008 $ ');
+---------------------------+
| trim(' $ beijing2008 $ ') |
+---------------------------+
| $ beijing2008 $ |
+---------------------------+
11)substring(str,x,y)函数:返回从字符串str中的第x位置起y个字符长度的字串.
eg: mysql> select substring('beijing2008',8,4),substring('beijing2008',1,7);
+------------------------------+------------------------------+
| substring('beijing2008',8,4) | substring('beijing2008',1,7) |
+------------------------------+------------------------------+
| 2008 | beijing |
+------------------------------+------------------------------+
今天就先写这么多,以后陆续更新
本文转自wiliiwin 51CTO博客，原文链接:http://blog.51cto.com/wiliiwin/200286
