MYSQL常用字符串函数
想更进一步加强自己在查询语句方面的能力,需要掌握常用函数。
字符串函数
(1).concat(s1,s2,…sn)
将s1,s2,…sn串联成一个字符串。
exp:
root@test 14:43>desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type| Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id| int(11)| YES||
NULL||
| t1| datetime | YES|| NULL||
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
root@test 14:44>select * from t1;
+------+---------------------+
| id| t1|
+------+---------------------+
|1 | 2012-05-13 12:27:12 |
|2 | 2012-05-13 12:27:12 |
|3 | 2012-05-13 12:27:12 |
+------+---------------------+
3 rows in set (0.00 sec)
root@test 14:44>SELECT
CONCAT('2013-05-',DAY(t1),' ',CURTIME()) FROM t1;
+------------------------------------------+
| CONCAT('2013-05-',DAY(t1),' ',CURTIME())
|
+------------------------------------------+
| 2013-05-13 14:44:14|
| 2013-05-13 14:44:14|
| 2013-05-13 14:44:14|
+------------------------------------------+
3 rows in set (0.00 sec)
(2).lower(str)
将字符串str字符变为小写。
exp:
root@test 14:44>select
lower('ALANG85');
+------------------+
| lower('ALANG85') |
+------------------+
| alang85|
+------------------+
(3).upper(str)
将字符串str字符变为大写。
exp:
root@test 14:47>select
upper('alang85');
+------------------+
| upper('alang85') |
+------------------+
| ALANG85|
(4).left(str,x)
返回字符串str最左边的x个字符。
exp:
root@test 14:49>select
left('chinaunix',2);
+---------------------+
| left('chinaunix',2) |
+---------------------+
| ch|
right(str,x)
返回字符串str最右边的x个字符。
exp:
root@test 14:49>select
right('chinaunix',2);
+----------------------+
| right('chinaunix',2) |
+----------------------+
| ix|
(5).lpad(str,n,pad)
用字符串pad对str最左边进行填充,直到长度为n个字符长度。
exp:
root@test 14:52>select
lpad('china',10,'alang85');
+----------------------------+
| lpad('china',10,'alang85') |
+----------------------------+
| alangchina|
(6).rpad(str,n,pad)
用字符串pad对str最右边进行填充,直到长度为n个字符长度。
exp:
root@test 14:53>select
rpad('china',10,'alang85');
+----------------------------+
| rpad('china',10,'alang85') |
+----------------------------+
| chinaalang|
(7).ltrim(str)
出掉字符串str左侧的空格。
exp:
root@test 14:58>select ltrim('alang85');
+---------------------------+
| ltrim('alang85') |
+---------------------------+
| alang85|
(8).rtrim(str)
出掉字符串str右侧的空格。
exp:
root@test 14:58>select rtrim('alang85');
+-----------------------------------+
| rtrim('alang85') |
+-----------------------------------+
|alang85|
(9).trim(str)
出掉字符串行首和行尾的空格。
exp:
root@test 14:58>select trim('alang85');
+----------------------------------+
| trim('alang85') |
+----------------------------------+
| alang85|
(10).repeat(str,x)
返回str重复x次的结果。
exp:
root@test 15:00>select
repeat('alang85',2);
+---------------------+
| repeat('alang85',2) |
+---------------------+
| alang85alang85|
(11).replace(str,a,b)
用字符串b替换str中所有出现的字符串a。
exp:
root@(none) 15:22>select
replace('alang85','85','china');
+---------------------------------+
| replace('alang85','85','china') |
+---------------------------------+
| alangchina|
(12).strcmp(s1,s2)
比较字符串s1和s2。s1与s2相等时返回0;s1大于s2时,返回1;s1小于s2时,返回-1。
exp:
root@test 15:01>select
strcmp('chinaunix','alang85');
+-------------------------------+
| strcmp('chinaunix','alang85') |
+-------------------------------+
|1 |
root@test 15:01>select
strcmp('alang85','chinaunix');
+-------------------------------+
| strcmp('alang85','chinaunix') |
+-------------------------------+
|-1 |
root@test 15:01>select
strcmp('alang85','alang85');
+-----------------------------+
| strcmp('alang85','alang85') |
+-----------------------------+
|0 |
(13).substring(str)
返回字符串str x位置起y个字符长度的字符串。
exp:
root@(none) 15:24>select
substring('chinaunix',6,4);
+----------------------------+
| substring('chinaunix',6,4) |
+----------------------------+
| unix|
其余几种用法:
mysql> SELECT
SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM
4);
-> 'barbar'mysql> SELECT SUBSTRING('Sakila', -3);
-> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5,
3);
-> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4
FOR 2);
-> 'ki'
(14).insert(str,x,y,instr)
将字符串str从第x位置开始,y个字符串长的字符串替换为instr。
exp:
root@(none) 15:26>select
insert('chinaunix',6,4,'alang');
+---------------------------------+
| insert('chinaunix',6,4,'alang') |
+---------------------------------+
| chinaalang|
(15).reverse(str)
将字符串str反向显示
root@(none) 15:44>select
reverse('alang85');
+--------------------+
| reverse('alang85') |
+--------------------+
| 58gnala|
(16)ascii(str)
返回字符串str的ascii数值,如果字符串为NULL返回NULL,如果字符长空字符返回0.
root@(none) 15:50>select ascii('b');
+------------+
| ascii('b') |
+------------+
| 98 |