mysql你不知道字符串操作函数
发布:smiling 来源: PHP粉丝网 添加日期:2014-10-02 21:47:49 浏览: 评论:0
LENGTH()函数可以获得一个字符串的长度,代码如下:
- select length('abcdefg'),length('0123456789');
- /*
- +-------------------+----------------------+
- | length('abcdefg') | length('0123456789') |
- +-------------------+----------------------+
- | 7 | 10 |
- +-------------------+----------------------+
- 1 row in set (0.00 sec)
- */
使用RPAD()或者LPAD()函数可以分别从右边或者左边对字符串进行填补,一般,字符串经常用空格进行填补,就是所谓的补齐,MySQL可以通过给RPAD()和LPAD()函数中指定一个自定义值作为第三个参数对字符串进行填补.
- select rpad('simaopig',17,''),rpad('simaopig',17,'@'),lpad('simaopig',17,''),lpad('simaopig',17,'@');
- /*
- +------------------------+-------------------------+------------------------+-------------------------+
- | rpad('simaopig',17,'') | rpad('simaopig',17,'@') | lpad('simaopig',17,'') | lpad('simaopig',17,'@') |
- +------------------------+-------------------------+------------------------+-------------------------+
- | NULL | simaopig@@@@@@@@@ | NULL | @@@@@@@@@simaopig |
- +------------------------+-------------------------+------------------------+-------------------------+
- 1 row in set (0.00 sec)
- */
- select rpad('simaopig',17,' '),rpad('simaopig',17,'@'),lpad('simaopig',17,' '),lpad('simaopig',17,'@');
- /*
- +-------------------------+-------------------------+-------------------------+-------------------------+
- | rpad('simaopig',17,' ') | rpad('simaopig',17,'@') | lpad('simaopig',17,' ') | lpad('simaopig',17,'@') |
- +-------------------------+-------------------------+-------------------------+-------------------------+
- | simaopig | simaopig@@@@@@@@@ | simaopig | @@@@@@@@@simaopig |
- +-------------------------+-------------------------+-------------------------+-------------------------+
- 1 row in set (0.00 sec)
- */
LTRIM()和RTRIM()函数产生与填补相反的效果,去除字符串的首和尾字符:
- select ltrim('simaopig '),ltrim(' simaopig'),rtrim('simaopig '),rtrim(' simaopig'),ltrim(rtrim(' simaopig '));
- /*
- +----------------------+---------------------------+----------------------+--------------------------+----------------------------------+
- | ltrim('simaopig ') | ltrim(' simaopig') | rtrim('simaopig ') | rtrim(' simaopig') | ltrim(rtrim(' simaopig ')) |
- +----------------------+---------------------------+----------------------+--------------------------+----------------------------------+
- | simaopig | simaopig | simaopig | simaopig | simaopig |
- +----------------------+---------------------------+----------------------+--------------------------+----------------------------------+
- 1 row in set (0.00 sec)
- */
即然谈到了"空格",那么把书中的例子再抄一遍,那就是MySQL提供了一个函数,这个函数(SPACE())的功能是只返回空格.
- select space(1),space(4),length(space(1)),length(space(4));
- /*
- +----------+----------+------------------+------------------+
- | space(1) | space(4) | length(space(1)) | length(space(4)) |
- +----------+----------+------------------+------------------+
- | | | 1 | 4 |
- +----------+----------+------------------+------------------+
- 1 row in set (0.00 sec)
- */
TRIM()函数可以指定去除格式,并且可以指定是去除左边还是右边?或者去除其它字符,不发局限于空格.
其实啊,就是把RTRIM()和LTRIM()的活都做了,而且做的好,可以说TRIM()和RTRIM()是TRIM()的子集,代码如下:
- select trim(' simaopig '),length(trim(' simaopig ')) as len;
- /*
- +--------------------+-----+
- | trim(' simaopig ') | len |
- +--------------------+-----+
- | simaopig | 8 |
- +--------------------+-----+
- 1 row in set (0.00 sec)
- */
- select trim(leading '!' from '!!!simaopig!!!') as trim_leading,trim(trailing '!' from '!!!simaopig!!!') as trim_trailing,trim(both '!' from '!!!simaopig!!!') as trim_both;
- /*
- +--------------+---------------+-----------+
- | trim_leading | trim_trailing | trim_both |
- +--------------+---------------+-----------+
- | simaopig!!! | !!!simaopig | simaopig |
- +--------------+---------------+-----------+
- 1 row in set (0.00 sec)
- */
LEFT()和RIGHT()函数可以获得字符串的指定部分,它将从字符串的左边或右边返回字符,代码如下:
- select left('simaopig',5) as left_five,right('simaopig',5) as right_five,left(right('simaopig',7),5) as midd_five;
- /*
- +-----------+------------+-----------+
- | left_five | right_five | midd_five |
- +-----------+------------+-----------+
- | simao | aopig | imaop |
- +-----------+------------+-----------+
- 1 row in set (0.00 sec)
- */
SUBSTRING()函数,允许指定一个字符串的长度和起始位置,也是获取子字符串,注意哈,这里的MySQL的字符串起始位置是1,不是零哈,代码如下:
- select substring('simaopig',2,3);
- /*
- +---------------------------+
- | substring('simaopig',2,3) |
- +---------------------------+
- | ima |
- +---------------------------+
- 1 row in set (0.00 sec)
- */
CONCAT()函数使其内提供的参数连接起来,如果CONCAT()的参数是空的,其结果也是空的(NULL),代码如下:
- select concat('welcome','to','小小子'),concat('simaopig',NULL);
- /*
- +------------------------------------+-------------------------+
- | concat('welcome','to','小小子') | concat('simaopig',NULL) |
- +------------------------------------+-------------------------+
- | welcometo小小子 | NULL |
- +------------------------------------+-------------------------+
- 1 row in set (0.00 sec)
- */
CONCAT_WS()函数与CONCAT()函数基本相同,只是其还可以提供一个参数作为连接后字符串的分隔符,代码如下:
- select concat_ws(',','welcome','to','xiaoxiaozi.com') as con_wel,concat_ws(' ','Simaopig','Love','Yatou') as con_love;
- /* --phpfensi.com
- +---------------------------+---------------------+
- | con_wel | con_love |
- +---------------------------+---------------------+
- | welcome,to,xiaoxiaozi.com | Simaopig Love Yatou |
- +---------------------------+---------------------+
- 1 row in set (0.00 sec)
- */
使用LOCATE()函数检验一个字符串是否包含所要检测的字符串,如果存在,返回其第一次出现的位置,今天,第二次强调,MySQL字符串起始位置为1,不是零,代码如下:
- select locate('pig','simaopig'),locate('','simaopig'),locate(' ','simaopig'),locate(' ',' simaopig ');
- /*
- +--------------------------+-----------------------+------------------------+--------------------------+
- | locate('pig','simaopig') | locate('','simaopig') | locate(' ','simaopig') | locate(' ',' simaopig ') |
- +--------------------------+-----------------------+------------------------+--------------------------+
- | 6 | 1 | 0 | 1 |
- +--------------------------+-----------------------+------------------------+--------------------------+
- 1 row in set (0.00 sec)
- */
FIND_IN_SET()函数是在一个大集合中查找指定的字符串,在MySQL中看到set就应该想到集合,代码如下:
- select find_in_set('simaopig','My,Name,Is,Simaopig,You,Can,Call,Me,simaopig,Too') as string_locate;
- /*
- +---------------+
- | string_locate |
- +---------------+
- | 4 |
- +---------------+
- 1 row in set (0.00 sec)
- */
哈,看到了吧,其返回的是该字符串是集合的第几个元素,嗯,你还问为啥大写的也返回了?呃,因为我没有用binary啊,STRCMP()函数比较两个字符串是否相同,相同返回0,如果第一个大于第二个返回1,否则返回-1.
呃,同样的,不加binary的话,字符串比较是不区分大小写的,代码如下:
- select strcmp('a','b'),strcmp('b','a'),strcmp('a','a'),strcmp('a','A'),strcmp('a','B');
- /*
- +-----------------+-----------------+-----------------+-----------------+-----------------+
- | strcmp('a','b') | strcmp('b','a') | strcmp('a','a') | strcmp('a','A') | strcmp('a','B') |
- +-----------------+-----------------+-----------------+-----------------+-----------------+
- | -1 | 1 | 0 | 0 | -1 |
- +-----------------+-----------------+-----------------+-----------------+-----------------+
- 1 row in set (0.00 sec)
- */
REPLACE()函数和在别的编程语言里的用法一致,替换功能,代码如下:
- select replace('I am simaopig','simaopig','xiaoxiaozi');
- /*
- +--------------------------------------------------+
- | replace('I am simaopig','simaopig','xiaoxiaozi') |
- +--------------------------------------------------+
- | I am xiaoxiaozi |
- +--------------------------------------------------+
- 1 row in set (0.00 sec)
- */
INSSERT()函数可以把一个字符串的指定部分,定义了起始位置和长度,替换为一个新值,代码如下:
- select insert('I am simaopig',6,8,'xiaoxiaozi');
- /*
- +------------------------------------------+
- | insert('I am simaopig',6,8,'xiaoxiaozi') |
- +------------------------------------------+
- | I am xiaoxiaozi |
- +------------------------------------------+
- 1 row in set (0.00 sec)
- */
REPEAT()函数用于重复字符串操作,例如,输出十遍'xiaoxiaozi',代码如下:
- select repeat('xiaoxiaozi',10);
- /*
- +------------------------------------------------------------------------------------------------------+
- | repeat('xiaoxiaozi',10) |
- +------------------------------------------------------------------------------------------------------+
- | xiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozixiaoxiaozi |
- +------------------------------------------------------------------------------------------------------+
- 1 row in set (0.00 sec)
- */
REVERSE()函数颠倒字符串,代码如下:
- select reverse('simaopig'),reverse(repeat('hello ',6));
- /*
- +---------------------+--------------------------------------+
- | reverse('simaopig') | reverse(repeat('hello ',6)) |
- +---------------------+--------------------------------------+
- | gipoamis | olleh olleh olleh olleh olleh olleh |
- +---------------------+--------------------------------------+
- 1 row in set (0.00 sec)
- */
UCASE()和LCASE()将字符串分别转化为大写和小写,代码如下:
- select ucase('simaopig'),ucase('Simaopig'),lcase('SIMAOPIG'),lcase('simaopiG');
- /*
- +-------------------+-------------------+-------------------+-------------------+
- | ucase('simaopig') | ucase('Simaopig') | lcase('SIMAOPIG') | lcase('simaopiG') |
- +-------------------+-------------------+-------------------+-------------------+
- | SIMAOPIG | SIMAOPIG | simaopig | simaopig |
- +-------------------+-------------------+-------------------+-------------------+
- 1 row in set (0.00 sec)
- */
ASCII()函数返回指定字符的ASCII码,代码如下:
- select ascii('y'),ascii('Simaopig'),ascii('simaopig');
- /*
- +------------+-------------------+-------------------+
- | ascii('y') | ascii('Simaopig') | ascii('simaopig') |
- +------------+-------------------+-------------------+
- | 121 | 83 | 115 |
- +------------+-------------------+-------------------+
- 1 row in set (0.00 sec)
- */
ORD()函数返回指定字符的数字编码,经常用于替代ASCII(),我咋感觉其返回值与ASCII()一模一样,PHP也有这个函数,貌似,代码如下:
- select ord('y'),ord('Simaopig'),ord('simaopig');
- /*
- +----------+-----------------+-----------------+
- | ord('y') | ord('Simaopig') | ord('simaopig') |
- +----------+-----------------+-----------------+
- | 121 | 83 | 115 |
- +----------+-----------------+-----------------+
- 1 row in set (0.00 sec)
- */
再补充一下
1.ASCII(str)
返回字符串str的最左面字符的ASCII代码值,如果str是空字符串,返回0,如果str是NULL,返回NULL.
- mysql> select ASCII('2');
- -> 50
- mysql> select ASCII(2);
- -> 50
- mysql> select ASCII('dx');
- -> 100也可参见ORD()函数。
2.ORD(str)
如果字符串str最左面字符是一个多字节字符,通过以格式((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]返回字符的ASCII代码值来返回多字节字符代码。如果最左面的字符不是一个多字节字符。返回与ASCII()函数返回的相同值。
mysql> select ORD('2');
-> 50
3.CONV(N,from_base,to_base)
在不同的数字基之间变换数字,返回数字N的字符串数字,从from_base基变换为to_base基,如果任何参数是NULL,返回NULL,参数N解释为一个整数,但是可以指定为一个整数或一个字符串,最小基是2且最大的基是36,如果to_base是一个负数,N被认为是一个有符号数,否则,N被当作无符号数,CONV以64位点精度工作.
- mysql> select CONV("a",16,2);
- -> '1010'
- mysql> select CONV("6E",18,8);
- -> '172'
- mysql> select CONV(-17,10,-18);
- -> '-H'
- mysql> select CONV(10+"10"+'10'+0xa,10,10);
- -> '40'
4.BIN(N)
返回二进制值N的一个字符串表示,在此N是一个长整数(BIGINT)数字,这等价于CONV(N,10,2),如果N是NULL,返回NULL.
mysql> select BIN(12);
-> '1100'
5.OCT(N)
返回八进制值N的一个字符串的表示,在此N是一个长整型数字,这等价于CONV(N,10,8)。如果N是NULL,返回NULL.
mysql> select OCT(12);
-> '14'
6.HEX(N)
返回十六进制值N一个字符串的表示,在此N是一个长整型(BIGINT)数字,这等价于CONV(N,10,16),如果N是NULL,返回NULL.
mysql> select HEX(255);
-> 'FF'
7.CHAR(N,...)
CHAR()将参数解释为整数并且返回由这些整数的ASCII代码字符组成的一个字符串,NULL值被跳过.
- mysql> select CHAR(77,121,83,81,'76');
- -> 'MySQL'
- mysql> select CHAR(77,77.3,'77.3');
- -> 'MMM'
Tags: mysql函数 mysql操作函数
相关文章
- ·mysql自定义函数实现中文首字母拼音查询(2014-09-23)
- ·mysql字符串处理函数整理(2014-09-24)
- ·mysql用户函数区别详解(2014-09-25)
- ·mysql中count函数使用方法详解(2014-09-27)
- ·mysql函数group_concat使用方法(2014-10-01)
- ·MySql中GROUP_CONCAT()函数使用详解(2014-10-03)
- ·MySQL存储过程的函数与基本用法(2014-10-05)
- ·mysql group_concat函数使用方法(2014-10-08)
- ·mysql过程去除html标签的函数例子(2015-04-17)
推荐文章
热门文章
最新评论文章
- 写给考虑创业的年轻程序员(10)
- PHP新手上路(一)(7)
- 惹恼程序员的十件事(5)
- PHP邮件发送例子,已测试成功(5)
- 致初学者:PHP比ASP优秀的七个理由(4)
- PHP会被淘汰吗?(4)
- PHP新手上路(四)(4)
- 如何去学习PHP?(2)
- 简单入门级php分页代码(2)
- php中邮箱email 电话等格式的验证(2)