|
| 3 |
+------+
filed(str,str1,str2,...):返回第一个与字符串str相匹配的字符串的位置
mysql> select field('mysql','oracle','sql server','mysql') as '位置';
+------+
| 位置 |
+------+
| 3 |
+------+
locate(str1,str):返回参数str中字符串str1的开始位置
position(str1 in str)
instr(str,str1)
mysql> select locate('sql','mysql') as'locate',position('sql' in 'mysql') as 'position',instr('mysql','sql') as 'instr';
+--------+----------+-------+
| locate | position | instr |
+--------+----------+-------+
| 3 | 3 | 3 |
+--------+----------+-------+
elt(n,str1,str2,...)
返回第n个字符串
mysql> select elt(1,'mysql','oracle','sql server') as ELT;
+-------+
| ELT |
+-------+
| mysql |
+-------+
make_set(num,str1,str2,...,strn)
首先将数值num转换成二进制数，然后按照二进制从参数str1，str2，...，strn中选取相应的字符串。在通过二进制来选择字符串时，会从右到左读取该值，如果值为1选择该字符串，否则将不选择字符串。
mysql> select bin(13) as BIN, make_set(13,'a','b','c','d','e','f','g') as make_set;
+------+----------+
| BIN | make_set |
+------+----------+
| 1101 | a,c,d |
+------+----------+
1 row in set (0.00 sec)
mysql> select bin(23) as BIN, make_set(23,'a','b','c','d','e','f','g') as make_set;
+-------+----------+
| BIN | make_set |
+-------+----------+
| 10111 | a,b,c,e |
+-------+----------+
1 row in set (0.00 sec)
mysql> select bin(23) as BIN, make_set(23,'a','b') as make_set;
+-------+----------+
| BIN | make_set |
+-------+----------+
| 10111 | a,b |
+-------+----------+
7、从现有字符串中截取子字符串
left(str,num)
mysql> select left('algfdg',3) as 'left';
+------+
| left |
+------+
| alg |
+------+
right(str,num)
mysql> select right('algfdg',3) as 'right';
+-------+
| right |
+-------+
| fdg |
+-------+
substring(str,num,len)和mid(str,num,len)：截取指定位置和长度的子字符串
mysql> select substring('algfdg',3,4) as 'substring',mid('algfdg',3,4) as 'mid';
+-----------+------+
| substring | mid |
+-----------+------+
| gfdg | gfdg |
+-----------+------+
8、去除字符串的首尾空格
ltrim(str)
mysql> select ltrim(' mysql') as ' mysql';
+-------+
| mysql |
+-------+
| mysql |
+-------+
1 row in set, 1 warning (0.00 sec)
mysql> select ' mysql' as ' mysql';
+----------+
| mysql |
+----------+
| mysql |
+----------+
1 row in set, 1 warning (0.00 sec)
right(str)
mysql> select rtrim('mysql ') as 'mysql';
+-------+
| mysql |
+-------+
| mysql |
+-------+
1 row in set (0.00 sec)
mysql> select 'mysql ' as 'mysql';
+-------------+
| mysql |
+-------------+
| mysql |
+-------------+
trim(str)
mysql> select trim(' mysql ') as ' mysql ';
+---------+
| mysql |
+---------+
| mysql |
+---------+
9、替换字符串
insert(str,pos,len,newstr):
mysql> select '这是mysql数据库' as 'oldstring',insert('这是mysql数据库',3,5,'oracle') as 'newstring';
+-----------------+------------------+
| oldstring | newstring |
+-----------------+------------------+
| 这是mysql数据库 | 这oracleql数据库 |
+-----------------+------------------+
replace(str,substr,newstr)
mysql> select '这是mysql数据库' as 'oldstring',replace('这是mysql数据库','mysql','oracle') as newstring;
+-----------------+------------------+
| oldstring | newstring |
+-----------------+------------------+
| 这是mysql数据库 | 这是oracle数据库 |
+-----------------+------------------+
数值函数
abs(x) 返回数值x的绝对值
ceil(x) 向上取整
float(x) 向下取整
mod(x,y) 返回x模y的值
rand() 返回0~1内的随机数
rand(n) 指定种子
round(x,y) 返回x的四舍五入后有y位小数的数值
truncate(x,y) 返回数值x截断y位小数的数值
mysql> select truncate(903.2432,2),truncate(902.3 |