MySQL之字符串函数
#每天进步一点点
select power((1+0.01),365)/power((1-0.01),365) as success;
+--------------------+
| success |
+--------------------+
| 1480.6601993481067 |
+--------------------+
1 row in set (0.03 sec)
1、字符长度函数
length(str)
char_length(str) #多字节的一个字符算作一次
mysql> select length('刘顺祥');
+------------------+
| length('刘顺祥') |
+------------------+
| 6 |
+------------------+
1 row in set (0.00 sec)
mysql> select length('我的名字叫刘顺祥');
+----------------------------+
| length('我的名字叫刘顺祥') |
+----------------------------+
| 16 |
+----------------------------+
1 row in set (0.06 sec)
发现对于汉字length函数默认一个汉字的长度为2。
mysql> select char_length('刘顺祥');
+-----------------------+
| char_length('刘顺祥') |
+-----------------------+
| 3 |
+-----------------------+
1 row in set (0.07 sec)
mysql> select char_length('我的名字叫刘顺祥');
+---------------------------------+
| char_length('我的名字叫刘顺祥') |
+---------------------------------+
| 8 |
+---------------------------------+
1 row in set (0.00 sec)
而char_length函数可以非常精确的将一个汉字算作一个长度，故该函数将多字节的一个字符算作一次
2、字符连接函数
concat(str1,str2,...); #注意，任意一个元素为NULL的话，返回值为NULL
concat_ws(seprator='',str1,str2,...); #指定某个字符为各个字符的分隔符
mysql> select concat('My','SQL');
+--------------------+
| concat('My','SQL') |
+--------------------+
| MySQL |
+--------------------+
1 row in set (0.08 sec)
mysql> select concat_ws(' ','My','SQL');
+---------------------------+
| concat_ws(' ','My','SQL') |
+---------------------------+
| My SQL |
+---------------------------+
1 row in set (0.02 sec)
3、返回子字符在字符串中首次出现的位置
locate(substr,str，pos) #从pos开始查找substr在str中首次出现的位置
position(substr in str)
instr(str,substr)
mysql> select locate('@','123456@789');
+--------------------------+
| locate('@','123456@789') |
+--------------------------+
| 7 |
+--------------------------+
1 row in set (0.13 sec)
mysql> select position('@' in '123456@789');
+-------------------------------+
| position('@' in '123456@789') |
+-------------------------------+
| 7 |
+-------------------------------+
1 row in set (0.21 sec)
mysql> select locate('@','123@456@789','6');
+-------------------------------+
| locate('@','123@456@789','6') |
+-------------------------------+
| 8 |
+-------------------------------+
1 row in set (0.01 sec)
mysql> select instr('liushunxiang','s');
+---------------------------+
| instr('liushunxiang','s') |
+---------------------------+
| 4 |
+---------------------------+
1 row in set (0.00 sec)
4、字符串填补
lpad(str,len,padstr) #在str左边填补padstr,并使str的长度为len,len的长度超过str+padstr的长度时，循环padstr
rpad(str,len,padstr)
mysql> select lpad('My',5,'SQL');
+--------------------+
| lpad('My',5,'SQL') |
+--------------------+
| SQLMy |
+--------------------+
1 row in set (0.00 sec)
mysql> select lpad('My',6,'SQL');
+--------------------+
| lpad('My',6,'SQL') |
+--------------------+
| SQLSMy |
+--------------------+
1 row in set (0.00 sec)
mysql> select rpad('My',5,'SQL');
+--------------------+
| rpad('My',5,'SQL') |
+--------------------+
| MySQL |
+--------------------+
1 row in set (0.00 sec)
mysql> select rpad('My',6,'SQL');
+--------------------+
| rpad('My',6,'SQL') |
+--------------------+
| MySQLS |
+--------------------+
1 row in set (0.00 sec)
mysql> select lpad(' ',10,' '); #填补空格
+------------------+
| lpad(' ',10,' ') |
+------------------+
| |
+------------------+
1 row in set (0.00 sec)
mysql> select length(lpad(' ',10,' '));
+--------------------------+
| length(lpad(' ',10,' ')) |
+--------------------------+
| 10 |
+--------------------------+
1 row in set (0.00 sec)
5、截取字符串
left(str,len)
right(str,len)
substring(str,stratpos,len)
mid(str,startpos,len)
substring_index(str,delimiter,count) #返回第count个出现delimiter之后/之前的子串。如果count为正，从左到右的子串，如果count为负，从右到左的子串
mysql> select left('liushunxiang',3);
+------------------------+
| left('liushunxiang',3) |
+------------------------+
| liu |
+------------------------+
1 row in set (0.07 sec)
mysql> select right('liushunxiang',length('liushunxiang')-3);
+------------------------------------------------+
| right('liushunxiang',length('liushunxiang')-3) |
+------------------------------------------------+
| shunxiang |
+------------------------------------------------+
1 row in set (0.02 sec)
mysql> select substring('mysql',3,3);
+------------------------+
| substring('mysql',3,3) |
+------------------------+
| sql |
+------------------------+
1 row in set (0.01 sec)
mysql> select mid('mysql',3,3);
+------------------+
| mid('mysql',3,3) |
+------------------+
| sql |
+------------------+
1 row in set (0.00 sec)
mysql> select substring_index('123@456@789','@',2);
+--------------------------------------+
| substring_index('123@456@789','@',2) |
+--------------------------------------+
| 123@456 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> select substring_index('123@456@789','@',-2);
+---------------------------------------+
| substring_index('123@456@789','@',-2) |
+---------------------------------------+
| 456@789 |
+---------------------------------------+
1 row in set (0.00 sec)
6、排除空格及特定字符
rtrim(str)
ltrim(str)
trim(str)
trim(both|leading|trailing remstr from str) #默认排除str首尾remstr,leading指排除前缀remstr，trailing指排除houzhuiremstr
mysql> select ltrim(lpad('name',20,' '));
+----------------------------+
| ltrim(lpad('name',20,' ')) |
+----------------------------+
| name |
+----------------------------+
1 row in set (0.00 sec)
mysql> select rtrim(rpad('name',20,' '));
+----------------------------+
| rtrim(rpad('name',20,' ')) |
+----------------------------+
| name |
+----------------------------+
1 row in set (0.00 sec)
mysql> select trim(' name ');
+--------------------+
| trim(' name ') |
+--------------------+
| name |
+--------------------+
1 row in set (0.02 sec)
mysql> select trim(both '@' from '@name@');
+------------------------------+
| trim(both '@' from '@name@') |
+------------------------------+
| name |
+------------------------------+
1 row in set (0.00 sec)
mysql> select trim(leading '@' from '@name@');
+---------------------------------+
| trim(leading '@' from '@name@') |
+---------------------------------+
| name@ |
+---------------------------------+
1 row in set (0.06 sec)
mysql> select trim(trailing '@' from '@name@');
+----------------------------------+
| trim(trailing '@' from '@name@') |
+----------------------------------+
| @name |
+----------------------------------+
1 row in set (0.00 sec)
7、产生空格
space(n)
mysql> select concat(space(20),'name');
+--------------------------+
| concat(space(20),'name') |
+--------------------------+
| name |
+--------------------------+
1 row in set (0.00 sec)
8、替换函数
replace(str,from_str,to_str) #用to_str替换from_str
insert(str,pos,len,newstr) #从pos开始的len个字符被newstr替换
mysql> select replace('123456@qq.com','qq','163');
+-------------------------------------+
| replace('123456@qq.com','qq','163') |
+-------------------------------------+
| 123456@163.com |
+-------------------------------------+
1 row in set (0.02 sec)
mysql> select insert('wahahahaohe',3,6,'mei');
+---------------------------------+
| insert('wahahahaohe',3,6,'mei') |
+---------------------------------+
| wameiohe |
+---------------------------------+
1 row in set (0.07 sec)
9、重复函数
repeat(str,count)
mysql> select concat(repeat(' ',20),'name');
+-------------------------------+
| concat(repeat(' ',20),'name') |
+-------------------------------+
| name |
+-------------------------------+
1 row in set (0.00 sec)
mysql> select repeat('abc',3);
+-----------------+
| repeat('abc',3) |
+-----------------+
| abcabcabc |
+-----------------+
1 row in set (0.00 sec)
10、翻转函数
reverse(str)
mysql> select reverse('mysql');
+------------------+
| reverse('mysql') |
+------------------+
| lqsym |
+------------------+
1 row in set (0.00 sec)
mysql> select reverse(reverse('mysql'));
+---------------------------+
| reverse(reverse('mysql')) |
+---------------------------+
| mysql |
+---------------------------+
1 row in set (0.00 sec)
11、elt(n,str1,str2,str3,...) #类似于R中switch函数，若n=1则返回str1,n=2返回str2...若n<1或n大于字符元素个数则返回NULL,该函数是field函数的反运算。
mysql> select elt(1,'liu','shun','xiang');
+-----------------------------+
| elt(1,'liu','shun','xiang') |
+-----------------------------+
| liu |
+-----------------------------+
1 row in set (0.00 sec)
mysql> select elt(2,'liu','shun','xiang');
+-----------------------------+
| elt(2,'liu','shun','xiang') |
+-----------------------------+
| shun |
+-----------------------------+
1 row in set (0.00 sec)
12、field(str,str1,str2,str3,...) #返回str在所有字符元素中的索引，该函数是elt函数的反运算。
mysql> select field('liu','liu','shun','xiang');
+-----------------------------------+
| field('liu','liu','shun','xiang') |
+-----------------------------------+
| 1 |
+-----------------------------------+
1 row in set (0.07 sec)
mysql> select field('xiang','liu','shuan','xiang');
+--------------------------------------+
| field('xiang','liu','shuan','xiang') |
+--------------------------------------+
| 3 |
+--------------------------------------+
1 row in set (0.00 sec)
13、大小写转换
upper(str)
Ucase(str)
lower(str)
Lcase(str)
mysql> select upper('liushunxiang');
+-----------------------+
| upper('liushunxiang') |
+-----------------------+
| LIUSHUNXIANG |
+-----------------------+
1 row in set (0.00 sec)
mysql> select ucase('liushunxiang');
+-----------------------+
| ucase('liushunxiang') |
+-----------------------+
| LIUSHUNXIANG |
+-----------------------+
1 row in set (0.00 sec)
mysql> select lower('LIUSHUNXIANG');
+-----------------------+
| lower('LIUSHUNXIANG') |
+-----------------------+
| liushunxiang |
+-----------------------+
1 row in set (0.00 sec)
mysql> select lcase('LIUSHUNXIANG');
+-----------------------+
| lcase('LIUSHUNXIANG') |
+-----------------------+
14文件内容读取函数
load_file(file_path) #读入文件内容，并作为一个字符串返回文件内容。