6.3　字符串函數

字符串函數主要用來處理數據庫中的字符串數據。MySQL中的字符串函數有計算字符串長度函數、字符串合并函數、字符串替換函數、字符串比較函數、查找指定字符串位置函數等。本節將介紹各種字符串函數的功能和用法。

6.3.1　計算字符串字符數的函數和字符串長度的函數

CHAR_LENGTH(str)返回值為字符串str所包含的字符個數。一個多字節字符算作一個單字符。

【例6.26】使用CHAR_LENGTH函數計算字符串字符個數，輸入語句如下：

LENGTH(str)返回值為字符串的字節長度，使用utf8（UNICODE的一種變長字符編碼，又稱萬國碼）編碼字符集時，一個漢字是3字節，一個數字或字母算1字節。

【例6.27】使用LENGTH函數計算字符串長度，輸入語句如下：

可以看到，計算的結果與CHAR_LENGTH相同，因為英文字符的個數和所占的字節相同，一個字符占1字節。

6.3.2　合并字符串函數CONCAT(s1,s2,…)、CONCAT_WS(x,s1,s2,…)

CONCAT(s1,s2,…)返回結果為連接參數產生的字符串，或許有一個或多個參數。如有任何一個參數為NULL，則返回值為NULL。如果所有參數均為非二進制字符串，則結果為非二進制字符串。如果自變量中含有任一二進制字符串，則結果為一個二進制字符串。

【例6.28】使用CONCAT函數連接字符串，輸入語句如下：

CONCAT('My SQL', '8.0')返回兩個字符串連接后的字符串；CONCAT('My',NULL, 'SQL')中有一個參數為NULL，因此返回結果為NULL。

在CONCAT_WS(x,s1,s2,…)中，CONCAT_WS代表CONCAT With Separator，是CONCAT()的特殊形式。第一個參數x是其他參數的分隔符，分隔符的位置放在要連接的兩個字符串之間。分隔符可以是一個字符串，也可以是其他參數。如果分隔符為NULL，則結果為NULL。函數會忽略任何分隔符參數后的NULL值。

【例6.29】使用CONCAT_WS函數連接帶分隔符的字符串，輸入語句如下：

CONCAT_WS('-', '1st','2nd', '3rd')使用分隔符‘-’將3個字符串連接成一個字符串，結果為“1st-2nd-3rd”；CONCAT_WS('*', '1st', NULL, '3rd')使用分隔符‘*’將兩個字符串連接成一個字符串，同時忽略NULL值。

6.3.3　替換字符串的函數INSERT(s1,x,len,s2)

INSERT(s1,x,len,s2)返回字符串s1，其子字符串起始于x位置和被字符串s2取代的len字符。如果x超過字符串長度，則返回值為原始字符串。假如len的長度大于其他字符串的長度，則從位置x開始替換。若任何一個參數為NULL，則返回值為NULL。

【例6.30】使用INSERT函數進行字符串替代操作，輸入語句如下：

第一個函數INSERT('Quest', 2, 4, 'What')將“Quest”第2個字符開始長度為4的字符串替換為What，結果為“QWhat”；第二個函數INSERT('Quest', -1, 4, 'What')中起始位置-1超出了字符串長度，直接返回原字符；第三個函數INSERT('Quest', 3, 100, 'What')替換長度超出了原字符串長度，則從第3個字符開始，截取后面所有的字符，并替換為指定字符What，結果為“QuWhat”。

6.3.4　字母大小寫轉換函數

LOWER (str)或者LCASE (str)可以將字符串str中的字母字符全部轉換成小寫字母。

【例6.31】使用LOWER函數或者LCASE函數將字符串中所有字母字符轉換為小寫，輸入語句如下：

由結果可以看到，原來所有字母為大寫的，全部轉換為小寫，如“BEAUTIFUL”，轉換之后為“beautiful”；大小寫字母混合的字符串，小寫不變，大寫字母轉換為小寫字母，如“WelL”，轉換之后為“well”。

UPPER(str)或者UCASE(str)可以將字符串str中的字母字符全部轉換成大寫字母。

【例6.32】使用UPPER函數或者UCASE函數將字符串中所有字母字符轉換為大寫，輸入語句如下：

由結果可以看到，原來所有字母字符為小寫的，全部轉換為大寫，如“black”，轉換之后為“BLACK”；大小寫字母混合的字符串，大寫不變，小寫字母轉換為大寫字母，如“BLacK”，轉換之后為“BLACK”。

6.3.5　獲取指定長度的字符串的函數LEFT(s,n)和RIGHT(s,n)

LEFT(s,n)返回字符串s開始的最左邊n個字符。

【例6.33】使用LEFT函數返回字符串中左邊的字符，輸入語句如下：

函數返回字符串“football”左邊開始的長度為5的子字符串，結果為“footb”。

RIGHT(s,n)返回字符串str最右邊的n個字符。

【例6.34】使用RIGHT函數返回字符串中右邊的字符，輸入語句如下：

函數返回字符串“football”右邊開始的長度為4的子字符串，結果為“ball”。

6.3.6　填充字符串的函數LPAD(s1,len,s2)和RPAD(s1,len,s2)

LPAD(s1,len,s2)返回字符串s1，其左邊由字符串s2填補到len字符長度。假如s1的長度大于len，則返回值被縮短至len字符。

【例6.35】使用LPAD函數對字符串進行填充操作，輸入語句如下：

字符串“hello”長度大于4，不需要填充，因此LPAD('hello',4,'??')只返回被縮短的長度為4的子串“hell”；字符串“hello”長度小于10，LPAD('hello',10,'??')返回結果為“?????hello”，左側填充‘？’，長度為10。

RPAD(s1,len,s2)返回字符串sl，其右邊被字符串s2填補至len字符長度。假如字符串s1的長度大于len，則返回值被縮短到len字符長度。

【例6.36】使用RPAD函數對字符串進行填充操作，輸入語句如下：

字符串“hello”長度大于4，不需要填充，因此RPAD('hello',4,'?')只返回被縮短的長度為4的子串“hell”；字符串“hello”長度小于10，RPAD('hello',10,'?')返回結果為“hello?????”，右側填充‘？’，長度為10。

6.3.7　刪除空格的函數LTRIM(s)、RTRIM(s)和TRIM(s)

LTRIM(s)返回字符串s，字符串左側空格字符被刪除。

【例6.37】使用LTRIM函數刪除字符串左邊的空格，輸入語句如下：

LTRIM只刪除字符串左邊的空格，而右邊的空格不會被刪除，“　book ”刪除左邊空格之后的結果為“book”。

RTRIM(s)返回字符串s，字符串右側空格字符被刪除。

【例6.38】使用RTRIM函數刪除字符串右邊的空格，輸入語句如下：

RTRIM只刪除字符串右邊的空格，左邊的空格不會被刪除，“　book ”刪除右邊空格之后的結果為“　book”。

TRIM(s)刪除字符串s兩側的空格。

【例6.39】使用TRIM函數刪除字符串兩側的空格，使用語句如下：

可以看到，函數執行之后字符串“　book　”兩邊的空格都被刪除，結果為“book”。

6.3.8　刪除指定字符串的函數TRIM(s1 FROM s)

TRIM(s1 FROM s)刪除字符串s中兩端所有的子字符串s1。s1為可選項，在未指定情況下，刪除空格。

【例6.40】使用TRIM(s1 FROM s)函數刪除字符串中兩端指定的字符，輸入語句如下：

刪除字符串“xyxboxyokxxyxy”兩端的重復字符串“xy”，而中間的“xy”并不刪除，結果為“xboxyokx”。

6.3.9　重復生成字符串的函數REPEAT(s,n)

REPEAT(s,n)返回一個由重復的字符串s組成的字符串，字符串s的數目等于n。若n<=0，則返回一個空字符串。若s或n為NULL，則返回NULL。

【例6.41】使用REPEAT函數重復生成相同的字符串，輸入語句如下：

REPEAT('MySQL', 3)函數返回的字符串由3個重復的“MySQL”字符串組成。

6.3.10　空格函數SPACE(n)和替換函數REPLACE(s,s1,s2)

SPACE(n)返回一個由n個空格組成的字符串。

【例6.42】使用SPACE函數生成由空格組成的字符串，輸入語句如下：

SPACE(6)返回的字符串由6個空格組成。

REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。

【例6.43】使用REPLACE函數進行字符串替代操作，輸入語句如下：

REPLACE('xxx.MySQL.com', 'x', 'w')將“xxx.MySQL.com”字符串中的‘x’字符替換為‘w’字符，結果為“www.MySQL.com”。

6.3.11　比較字符串大小的函數STRCMP(s1,s2)

STRCMP(s1,s2)：若所有的字符串均相同，則返回0；若根據當前分類次序，第一個參數小于第二個，則返回-1；其他情況返回1。

【例6.44】使用STRCMP函數比較字符串大小，輸入語句如下：

“txt”小于“txt2”，因此STRCMP('txt', 'txt2')返回結果為-1，STRCMP('txt2', 'txt')返回結果為1；“txt”與“txt”相等，因此STRCMP('txt', 'txt')返回結果為0。

6.3.12　獲取子串的函數SUBSTRING(s,n,len)和MID(s,n,len)

SUBSTRING(s,n,len)帶有len參數的格式，從字符串s返回一個長度與len字符相同的子字符串，起始于位置n。也可能對n使用一個負值。假若這樣，則子字符串的位置起始于字符串結尾的n字符，即倒數第n個字符，而不是字符串的開頭位置。

【例6.45】使用SUBSTRING函數獲取指定位置處的子字符串，輸入語句如下：

SUBSTRING('breakfast',5)返回從第5個位置開始到字符串結尾的子字符串，結果為“kfast”；SUBSTRING('breakfast',5,3)返回從第5個位置開始長度為3的子字符串，結果為“kfa”；SUBSTRING('lunch', -3)返回從結尾開始第3個位置到字符串結尾的子字符串，結果為“nch”；SUBSTRING('lunch', -5, 3)返回從結尾開始第5個位置，即字符串開頭起，長度為3的子字符串，結果為“lun”。

MID(s,n,len)與SUBSTRING(s,n,len)的作用相同。

【例6.46】使用MID()函數獲取指定位置處的子字符串，輸入語句如下：

可以看到MID和SUBSTRING的結果是一樣的。

6.3.13　匹配子串開始位置的函數

LOCATE(str1,str)、POSITION(str1 IN str)和INSTR(str, str1)3個函數的作用相同，返回子字符串str1在字符串str中的開始位置。

【例6.47】使用LOCATE、POSITION、INSTR函數查找字符串中指定子字符串的開始位置，輸入語句如下：

子字符串“ball”在字符串“football”中從第5個字母位置開始，因此3個函數返回結果都為5。

6.3.14　字符串逆序的函數REVERSE(s)

REVERSE(s)將字符串s反轉，返回的字符串的順序和s字符串順序相反。

【例6.48】使用REVERSE函數反轉字符串，輸入語句如下：

可以看到，字符串“abc”經過REVERSE函數處理之后所有字符串順序被反轉，結果為“cba”。

6.3.15　返回指定位置的字符串的函數

ELT(N，字符串1，字符串2，字符串3，...，字符串N)：若N = 1，則返回值為字符串1；若N=2，則返回值為字符串2；以此類推；若N小于1或大于參數的數目，則返回值為NULL。

【例6.49】使用ELT函數返回指定位置字符串，輸入語句如下：

由結果可以看到，ELT(3,'1st','2nd','3rd')返回第3個位置的字符串“3rd”；ELT(3,'net','os')指定返回字符串位置超出參數個數，返回NULL。

6.3.16　返回指定字符串位置的函數FIELD(s,s1,s2,…,sn)

FIELD(s,s1,s2,…,sn)返回字符串s在列表s1,s2,…,sn中第一次出現的位置，在找不到s的情況下，返回值為0。如果s為NULL，則返回值為0，原因是NULL不能同任何值進行同等比較。

【例6.50】使用FIELD函數返回指定字符串第一次出現的位置，輸入語句如下：

在FIELD('Hi', 'hihi', 'Hey', 'Hi', 'bas')函數中，字符串“Hi”出現在列表的第3個字符串位置，因此返回結果為3；FIELD('Hi', 'Hey', 'Lo', 'Hilo',　'foo')列表中沒有字符串“Hi”，因此返回結果為0。

6.3.17　返回子串位置的函數FIND_IN_SET(s1,s2)

FIND_IN_SET(s1,s2)返回字符串s1在字符串列表s2中出現的位置，字符串列表是一個由多個逗號‘,’分開的字符串組成的列表。如果s1不在s2或s2為空字符串，則返回值為0。如果任意一個參數為NULL，則返回值為NULL。這個函數在第一個參數包含一個逗號‘,’時將無法正常運行。

【例6.51】使用FIND_IN_SET()函數返回子字符串在字符串列表中的位置，輸入語句如下：

雖然FIND_IN_SET()和FIELD()兩個函數格式不同，但作用類似，都可以返回指定字符串在字符串列表中的位置。

6.3.18　選取字符串的函數MAKE_SET(x,s1,s2,…,sn)

MAKE_SET(x,s1,s2,…,sn)函數按x的二進制數從s1，s2,…,sn中選取字符串。例如5的二進制是0101，這個二進制從右往左的第1位和第3位是1，所以選取s1和s3。s1,s2,...,sn中的NULL值不會被添加到結果中。

【例6.52】使用MAKE_SET根據二進制位選取指定字符串，輸入語句如下：

1的二進制值為0001，4的二進制值為0100，1與4進行或操作之后的二進制值為0101，從右到左第1位和第3位為1。MAKE_SET(1,'a','b','c')返回第1個字符串；SET(1 | 4,'hello','nice','world')返回從左端開始第1個和第3個字符串組成的字符串；NULL不會添加到結果中，因此SET(1 | 4,'hello','nice',NULL,'world')只返回第1個字符串‘hello’；SET(0,'a','b','c')返回空字符串。