MYSQL常用字符串函数宝典

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 |