1. 計算字符串字符數和字符串長度 - CHAR_LENGTH(s)
CHAR_LENGTH(str): 返回str所包含的字符個數。
mysql> select CHAR_LENGTH('MySQL');
+----------------------+
| CHAR_LENGTH('MySQL') |
+----------------------+
| 5 |
+----------------------+
2. 合並字符 - CONCAT(s1,s2,...) 與 CONCAT_WS(x,s1,s2,...)
CONCAT(s1,s2,...):回結果為連接參數產生的字符串，或許有一個或者多個參數。如果有任何一個返回值為NULL, 則返回值為NULL.
mysql> select CONCAT('MySQL',' ','5.5',' ',NULL,'Function');
+-----------------------------------------------+
| CONCAT('MySQL',' ','5.5',' ',NULL,'Function') |
+-----------------------------------------------+
| NULL |
+-----------------------------------------------+
CONCAT_WS(x,s1,s2,...): 代表CONCAT With Separator, 是CONCAT的特殊形式。第一個參數Ｘ是其它參數的分隔符，分隔符的位置在要連接的字符串之間。分隔符可以是一個字符串，也可是其他參數。如果分隔符為NULL,則結果為NULL。
mysql> select CONCAT_WS('.','David','Tian'), CONCAT_WS(NULL,'MySQL','5.5');
+-------------------------------+-------------------------------+
| CONCAT_WS('.','David','Tian') | CONCAT_WS(NULL,'MySQL','5.5') |
+-------------------------------+-------------------------------+
| David.Tian | NULL |
+-------------------------------+-------------------------------+
3. 替換字符串函數 - INSERT(s1, x, len, s2)
INSERT(s1,x,len,s2):返回字符串s1, 其子字符串起始於x位置和被字符串s2取代的len字符。如果x超過字符串長度，則返回值為原始字符串。假如len的長度大於其它字符串的長度，則從位置x開始替換。若任何一個參數為NULL, 則返回值為NULL。
mysql> select INSERT('Softtekian',2,4,'!@#$') AS c1,
-> INSERT('Softtekian',-1,4,'@@@@') as c2,
-> INSERT('Softtekian',3,100,'$$') as c3,
-> INSERT('Softtekian',2,4,'%@') as c4;
+------------+------------+------+----------+
| c1 | c2 | c3 | c4 |
+------------+------------+------+----------+
| S!@#$ekian | Softtekian | So$$ | S%@ekian |
+------------+------------+------+----------+
4. 字母大小寫轉換函數- LOWER(s), LCASE(s), UPPER(s), UCASE(s)
LOWER(str)和LCASE(str):將字符串str中的字母全部轉換成小寫字母。
mysql> select LOWER('MySQL and Oracle ASM') as c1, LCASE('Database Administrator') as c2;
+----------------------+------------------------+
| c1 | c2 |
+----------------------+------------------------+
| mysql and oracle asm | database administrator |
+----------------------+------------------------+
UPPER(str)和UCASE(str):可以將字符串str中的字母全部轉換成大寫字母。
mysql> select UPPER('sunshine.ma') c1,UCASE('Sunshine.Ma') c2;
+-------------+-------------+
| c1 | c2 |
+-------------+-------------+
| SUNSHINE.MA | SUNSHINE.MA |
+-------------+-------------+
5. 獲取指定長度字符串：LEFT(s,n), RIGHT(s,n)
LEFT(s,n): 返回字符串s開始最左邊n個字符。
mysql> select LEFT('this is a testing email',7) as c1;
+---------+
| c1 |
+---------+
| this is |
+---------+
RIGHT(s,n):返回字符串str最右邊n個字符。
mysql> select RIGHT('this is a testing email',7) as c1;
+---------+
| c1 |
+---------+
| g email |
+---------+
6. 填充字符串函數：LPAD(s1, len, s2), RPAD(s1, len, s2)
LPAD(s1, len, s2): 返回字符串s1，其左邊由字符串s2填補到len字符長度。假如s1的長度大於len, 則返回值縮短至len字符。
mysql> select LPAD('Hello',4,'%%') as c1, LPAD('Hello',10,'*') as c2;
+------+------------+
| c1 | c2 |
+------+------------+
| Hell | *****Hello |
+------+------------+
RPAD(s1, len, s2): 返回字符串s1, 其右邊被字符串s2填補至len字符串s1的長度大於len, 則返值被縮短到len字符長度。
mysql> select RPAD('Hello',4,'%') as c1, RPAD('Hello',10,'*') as c2;
+------+------------+
| c1 | c2 |
+------+------------+
| Hell | Hello***** |
+------+------------+
7. 刪除空格字符串函數：LTRIM(s), RTRIM(s), TRIM(s)
LTRIM(s): 返回字符串s,字符串左側空格字符被刪除。
mysql> select LTRIM(' book ') as c1;
+---------+
| c1 |
+---------+
| book |
+---------+
RTRIM(s): 返回字符串s,字符串右側空格字符被刪除。
mysql> select RTRIM(' book ') as c1;
+---------+
| c1 |
+---------+
| book |
+---------+
TRIM(s): 返回字符串s,字符串兩側空格字符被刪除。
mysql> select TRIM(' book ') as c1;
+------+
| c1 |
+------+
| book |
+------+
8. 刪除指定字符串的函數：TRIM(s1 FROM s)
TRIM(s1 FROM s): 刪除字符串s中兩端所有的子字符串s1。s1為可選項，在未指定情況下，刪除空格。
mysql> select TRIM(' boook ') as c1, TRIM('xy' FROM 'xyxyDxyDxyxy') as c2;
+-------+------+
| c1 | c2 |
+-------+------+
| boook | DxyD |
+-------+------+
9. 重復生成字符串的函數：REPEAT(s,n)
REPEAT(s,n): 返回一個由重復的字符串s組成的字符串，字符串s的數目等於n。若n<=0,則返回一個空字符串。若s或n為NULL,則返回NULL。
mysql> select REPEAT('abc',3) as c1, REPEAT('abc',-1) as c2, REPEAT('abc',NULL) as c3;
+-----------+------+------+
| c1 | c2 | c3 |
+-----------+------+------+
| abcabcabc | | NULL |
+-----------+------+------+
10. 空格函數：SPACE(n)
SPACE(n):返回一個由n個空格組成的字符串。
mysql> select CONCAT('(',SPACE(6),')') AS c1, CHAR_LENGTH(SPACE(6)) AS C2;
+----------+----+
| c1 | C2 |
+----------+----+
| ( ) | 6 |
11. 替換函數：REPLACE(s,s1,s2)
REPLACE(s,s1,s2):使用字符串s2替代字符串s中所有的字符串s1。
mysql> select REPLACE('xxx.mysql.com','x','w') as c1;
+---------------+
| c1 |
+---------------+
| www.mysql.com |
12. 比較字符串大小函數：STRCMP(s1,s2)
STRCMP(s1,s2):若所有的字符串均相同，則返回0；若根據當前分類次序，第一個參數小於第二個，則返回-1,其它情況返回1。
mysql> select STRCMP('txt','txta') as c1, STRCMP('txta','txt') as c2, STRCMP('txt','txt') as c3;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| -1 | 1 | 0 |
+----+----+----+
13. 字符串截取函數：SUBSTRING(s,n,len), MID(s,n,len)
SUBSTRING(s,n,len):從字符串s返回一個長度為len的子字符串，起始位置為n。若n為負數，則子字符串的位置起始於字符串結尾的n個字符，即倒數第n個字符。若len省略，則取至結尾。
mysql> select SUBSTRING('breaskfast',5) as c1,
-> SUBSTRING('breaskfast',5,3) as c2,
-> SUBSTRING('breakfast',-3) as c3,
-> SUBSTRING('breakfast',-5,3) as c4;
+--------+-----+-----+-----+
| c1 | c2 | c3 | c4 |
+--------+-----+-----+-----+
| skfast | skf | ast | kfa |
+--------+-----+-----+-----+
MID(s,n,len): 與SUBSTRING(s,n,len)作用相同。
mysql> select MID('breaskfast',5) as c1, MID('breaskfast',5,3) as c2, MID('breakfast',-3) as c3, MID('breakfast',-5,3) as c4;+--------+-----+-----+-----+
| c1 | c2 | c3 | c4 |
+--------+-----+-----+-----+
| skfast | skf | ast | kfa |
+--------+-----+-----+-----+
14. 匹配子串開始位置函數：LOCATE(s1,s2), POSITION(s1 IN s2), INSTR(s2,s1)
LOCATE(s1,s2): 返回子字符串s1在字符串s2中的開始位置。
POSITION(s1 IN s2): 返回子字符串s1在字符串s2中的開始位置。
INSTR(s2,s1):返回子字符串s1在字符串s2中的開始位置。
mysql> select LOCATE('ball','football') c1,
-> POSITION('ball' IN 'football') c2,
-> INSTR('football','ball') c3;
+----+----+----+
| c1 | c2 | c3 |
+----+----+----+
| 5 | 5 | 5 |
+----+----+----+
15. 字符串逆序函數：REVERSE(s)
REVERSE(s): 將字符串s反轉，返回的字符串的順序和s字符串順序相反。
mysql> select REVERSE('I love you') as c1;
+------------+
| c1 |
+------------+
| uoy evol I |
+------------+
16. 返回指定位置的字符串函數：ELT(n,s1,s2,s3,...,Sn)
ELT(n,s1,s2,s3,...,Sn): 若n=1，則返回字符串S1，若n=2,則返回字符串S2,依此類推。若n小於1或大於參數的數目，則返回值為NULL。
mysql> select ELT(3,'1st','2nd','3rd') as c1, ELT(3,'oracle','MySQL') as c2;
+------+------+
| c1 | c2 |
+------+------+
| 3rd | NULL |
+------+------+
17. 返回指定字符串位置的函數：FIELD(s,s1,s2,...)
FIELD(s,s1,s2,...):返回字符串s在列表s1,s2,...中第一次出現的位置，在找不到s的情況下，返回值為0。如果s為NULL，則返回值為0，原因是NULL不能同任何值進行同等比較。
mysql> select FIELD('Hi','hihi','Hey','Hi','bas','ciao') as c1, FIELD('Hi','Hey','Lo','Hilo','foo') as c2;
+----+----+
| c1 | c2 |
+----+----+
| 3 | 0 |
+----+----+
18. 返回子串位置的函數：FIND_IN_SET(s1,s2)
FIND_IN_SET(s1,s2): 返回字符串s1在字符串s2中出現的位置，字符串列表是一個由多個逗號“,”分開的字符串組成的列表。如果s1不在s2中或s2為空字符串，則返回0。如果任何一個參數為NULL，則返回值為NULL。S1中不能包含一個逗號“,”。
mysql> select FIND_IN_SET('Hi','hihi,Hey,Hi,bas') as c1;
+----+
| c1 |
+----+
| 3 |
+----+
19. 選取字符串的函數：MAKE_SET(x,s1,s2,...)
MAKE_SET(x,s1,s2,...): 返回由x的二進制數指定的相應位的字符串組成的字符串，s1對應比特1,s2對應比特01,依此類推。s1,s2...中的NULL值不會被添加到結果中。
mysql> select MAKE_SET(1,'a','b','c') as c1, MAKE_SET(1|4,'hello','nice','world') as c2, MAKE_SET(1|4,'hello','nice',NULL,'world') as c3, MAKE_SET(0,'a','b','c') as c4;
+----+-------------+-------+----+
| c1 | c2 | c3 | c4 |
+----+-------------+-------+----+
| a | hello,world | hello | |
+----+-------------+-------+----+
說明：
1的二進制值為0001, 4的二進制值為0100，1和4進行或操作之後的二進制值為0101，從右到左第1位和第3位為１。
MAKE_SET(1,’a’,’b’,’c’): 返回第1個字符串；
MAKE_SET(1|4,'hello','nice','world')：返回從左端開始第1和第3個字符組成的字符串；
MAKE_SET(1|4,'hello','nice',NULL,'world')：NULL值不會添加到結果中，因此只會返回第一個字符串；
MAKE_SET(0,'a','b','c'): 返回空字符串。
如果您們在嘗試的過程中遇到什麼問題或者我的代碼有錯誤的地方，請給予指正，非常感謝！
聯系方式：[email protected]
版權＠：轉載請標明出處，否則追究法律責任，後果自負！
