MySQL常用字符函數簡介

concat函數，把傳入的參數連接成為一個字符串。
例如：
把“aaa”、“bbb”、”ccc”3個字符串連接成一個字符串，“aaabbbccc”.另外任何與NULL進行連接的結果都將是NULL.

>SELECT concat(‘aaa’,’bbb’,’ccc’),concat(‘aaa’,NULL);

mysql> SELECT concat('aaa','bbb','ccc'),concat('aaa',NULL);
+---------------------------+--------------------+
| concat('aaa','bbb','ccc') | concat('aaa',NULL) |
+---------------------------+--------------------+
| aaabbbccc                 | NULL               |
+---------------------------+--------------------+
1 row in set (0.00 sec)

insert(str,x,y,instr)函數：將字符串str從第x位置開始，y個字符長的子串替換成”me”.

mysql> SELECT insert('beijingaiNI',10,2,'WO');
+---------------------------------+
| insert('beijingaiNI',10,2,'WO') |
+---------------------------------+
| beijingaiWO                     |
+---------------------------------+
1 row in set (0.00 sec)

LOWER(str)和UPPER(str)函數：把字符串轉換成小寫或大寫
在字符串比較中，通常要將比較的字符串全部轉換為大寫或者小寫，如下例所示：

mysql> SELECT lower('WOAINI'),upper('woxihuanni');
+-----------------+---------------------+
| lower('WOAINI') | upper('woxihuanni') |
+-----------------+---------------------+
| woaini          | WOXIHUANNI          |
+-----------------+---------------------+
1 row in set (0.00 sec)

LEFT(str,x)和RIGHT(str,x)函數：分別返回字符串最左邊和最右邊的x個字符，如果第二個參數是NULL，那么將不返回任何字符串。
下例所示：

mysql> SELECT left('beijing',3);
+-------------------+
| left('beijing',3) |
+-------------------+
| bei               |
+-------------------+
1 row in set (0.01 sec)

mysql> SELECT left('beijing',NULL);
+----------------------+
| left('beijing',NULL) |
+----------------------+
| NULL                 |
+----------------------+
1 row in set (0.00 sec)

LPAD(str,n,pad)和RPAD(str,n,pad)函數：用字符串pad對str最左邊和最右邊進行填充，直到長度為n個字符串。

mysql> SELECT lpad('beijing',10,'123');
+--------------------------+
| lpad('beijing',10,'123') |
+--------------------------+
| 123beijing               |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT rpad('beijing',10,'123');
+--------------------------+
| rpad('beijing',10,'123') |
+--------------------------+
| beijing123               |
+--------------------------+
1 row in set (0.00 sec)

LTRIM(str)和RTRIM(str)函數：去掉字符串str左側和右側的空格。

mysql> SELECT ltrim('  |wo|'),rtrim('|ni|   ');
+-----------------+------------------+
| ltrim('  |wo|') | rtrim('|ni|   ') |
+-----------------+------------------+
| |wo|            | |ni|             |
+-----------------+------------------+
1 row in set (0.00 sec)

REPEAT(str,x)函數：返回str重復x次的結果。

mysql> SELECT repeat('beijing',3);
+-----------------------+
| repeat('beijing',3)   |
+-----------------------+
| beijingbeijingbeijing |
+-----------------------+
1 row in set (0.00 sec)

REPLACE(str,a,b)函數：用字符串b替換字符串str中所有出現的字符串a.

mysql> SELECT replace('beijing','bei','nan');
+--------------------------------+
| replace('beijing','bei','nan') |
+--------------------------------+
| nanjing                        |
+--------------------------------+
1 row in set (0.00 sec)

STRCMP(s1,s2)函數：比較字符串s1和s2的ASCII碼值的大小。
如果s1比s2小，那么就返回-1,相等返回0,大于返回1.

mysql> SELECT strcmp('a','b'),strcmp('b','b'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('b','b') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
|              -1 |               0 |               1 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

TRIM(str)函數：去掉目標字符串的開頭和結尾的空格。

mysql> SELECT trim('  ab  ');
+----------------+
| trim('  ab  ') |
+----------------+
| ab             |
+----------------+
1 row in set (0.00 sec)

SUBSTRING(str,x,y)函數：返回從字符串str中的第x位置起y個字符長度的字串。

mysql> SELECT substring('beijing2017',8,4);
+------------------------------+
| substring('beijing2017',8,4) |
+------------------------------+
| 2017                         |
+------------------------------+
1 row in set (0.00 sec)