.jpg)
mysql字符串函數(shù)有：1、LOWER，將字符串參數(shù)值轉(zhuǎn)換為全小寫(xiě)字母后返回；2、UPPER，將字符串參數(shù)值轉(zhuǎn)換為全大寫(xiě)字母后返回；3、CONCAT，將多個(gè)字符串參數(shù)首尾相連后返回；4、SUBSTR，從源字符串str中指定位置pos開(kāi)始取。
mysql字符串函數(shù)有：
1、LOWER(column|str)：將字符串參數(shù)值轉(zhuǎn)換為全小寫(xiě)字母后返回
mysql>?select?lower('SQL?Course');+---------------------+ |?lower('SQL?Course')?| +---------------------+ |?sql?course??????????| +---------------------+
?
2、UPPER(column|str)：將字符串參數(shù)值轉(zhuǎn)換為全大寫(xiě)字母后返回
mysql>?select?upper('Use?MYsql');+--------------------+ |?upper('Use?MYsql')?| +--------------------+ |?USE?MYSQL??????????| +--------------------+
?
3、CONCAT(column|str1, column|str2,…)：將多個(gè)字符串參數(shù)首尾相連后返回
mysql>?select?concat('My','S','QL');+-----------------------+ |?concat('My','S','QL')?| +-----------------------+ |?MySQL?????????????????| +-----------------------+
如果有任何參數(shù)為null，則函數(shù)返回null
mysql>?select?concat('My',null,'QL');+------------------------+ |?concat('My',null,'QL')?| +------------------------+ |?NULL???????????????????| +------------------------+
如果參數(shù)是數(shù)字，則自動(dòng)轉(zhuǎn)換為字符串
mysql>?select?concat(14.3,'mysql');+----------------------+ |?concat(14.3,'mysql')?| +----------------------+ |?14.3mysql????????????| +----------------------+
?
4、CONCAT_WS(separator,str1,str2,…)：將多個(gè)字符串參數(shù)以給定的分隔符separator首尾相連后返回
mysql>?select?concat_ws(';','First?name','Second?name','Last?name');+-------------------------------------------------------+ |?concat_ws(';','First?name','Second?name','Last?name')?| +-------------------------------------------------------+ |?First?name;Second?name;Last?name??????????????????????| +-------------------------------------------------------+
！！也就是函數(shù)圓括號(hào)里的第一個(gè)項(xiàng)目用來(lái)指定分隔符
5、SUBSTR(str,pos[,len])：從源字符串str中的指定位置pos開(kāi)始取一個(gè)字串并返回
注意：
①len指定子串的長(zhǎng)度，如果省略則一直取到字符串的末尾；len為負(fù)值表示從源字符串的尾部開(kāi)始取起。
②函數(shù)SUBSTR()是函數(shù)SUBSTRING()的同義詞。
mysql>?select?substring('hello?world',5);+----------------------------+ |?substring('hello?world',5)?| +----------------------------+ |?o?world????????????????????| +----------------------------+mysql>?select?substr('hello?world',5,3);+---------------------------+ |?substr('hello?world',5,3)?| +---------------------------+ |?o?w???????????????????????| +---------------------------+mysql>?select?substr('hello?world',-5);+--------------------------+ |?substr('hello?world',-5)?| +--------------------------+ |?world????????????????????| +--------------------------+
?
6、LENGTH(str)：返回字符串的存儲(chǔ)長(zhǎng)度
mysql>?select?length('text'),length('你好');+----------------+------------------+ |?length('text')?|?length('你好')???| +----------------+------------------+ |??????????????4?|????????????????6?| +----------------+------------------+
注意：編碼方式不同字符串的存儲(chǔ)長(zhǎng)度就不一樣(‘你好’:utf8是6，gbk是4)
?
7、CHAR_LENGTH(str)：返回字符串中的字符個(gè)數(shù)
mysql>?select?char_length('text'),char_length('你好');+---------------------+-----------------------+ |?char_length('text')?|?char_length('你好')???| +---------------------+-----------------------+ |???????????????????4?|?????????????????????2?| +---------------------+-----------------------+
?
8、INSTR(str, substr)：從源字符串str中返回子串substr第一次出現(xiàn)的位置
mysql>?select?instr('foobarbar','bar');+--------------------------+ |?instr('foobarbar','bar')?| +--------------------------+ |????????????????????????4?| +--------------------------+
?
9、LPAD(str, len, padstr)：在源字符串的左邊填充給定的字符padstr到指定的長(zhǎng)度len，返回填充后的字符串
mysql>?select?lpad('hi',5,'??');+-------------------+ |?lpad('hi',5,'??')?| +-------------------+ |????hi?????????????| +-------------------+
?
10、RPAD(str, len, padstr)：在源字符串的右邊填充給定的字符padstr到指定的長(zhǎng)度len，返回填充后的字符串
mysql>?select?rpad('hi',6,'??');+-------------------+|?rpad('hi',6,'??')?|+-------------------+|?hi????????????????|+-------------------+
?
11、TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)：
從源字符串str中去掉兩端、前綴或后綴字符remstr并返回；
如果不指定remstr，則去掉str兩端的空格；不指定BOTH、LEADING、TRAILING ，則默認(rèn)為 BOTH。
mysql>?select?trim('??bar??');+-----------------+ |?trim('??bar??')?| +-----------------+ |?bar?????????????| +-----------------+mysql>?select?trim(leading?'x'?from?'xxxbarxxx');+------------------------------------+ |?trim(leading?'x'?from?'xxxbarxxx')?| +------------------------------------+ |?barxxx?????????????????????????????| +------------------------------------+mysql>?select?trim(both?'x'?from?'xxxbarxxx');+---------------------------------+ |?trim(both?'x'?from?'xxxbarxxx')?| +---------------------------------+ |?bar?????????????????????????????| +---------------------------------+mysql>?select?trim(trailing?'xyz'?from?'barxxyz');+-------------------------------------+ |?trim(trailing?'xyz'?from?'barxxyz')?| +-------------------------------------+ |?barx????????????????????????????????| +-------------------------------------+
?
12、REPLACE(str, from_str, to_str)：在源字符串str中查找所有的子串form_str（大小寫(xiě)敏感），找到后使用替代字符串to_str替換它。返回替換后的字符串
mysql>?select?replace('www.mysql.com','w','Ww');+-----------------------------------+ |?replace('www.mysql.com','w','Ww')?| +-----------------------------------+ |?WwWwWw.mysql.com??????????????????| +-----------------------------------+
?
13、LTRIM(str)，RTRIM(str)：去掉字符串的左邊或右邊的空格(左對(duì)齊、右對(duì)齊)
mysql>?SELECT??ltrim('???barbar???')?rs1,?rtrim('???barbar???')?rs2;+-----------+-----------+ |?rs1???????|?rs2???????| +-----------+-----------+ |?barbar????|????barbar?| +-----------+-----------+
?
14、REPEAT(str, count)：將字符串str重復(fù)count次后返回
mysql>?select?repeat('MySQL',3);+-------------------+ |?repeat('MySQL',3)?| +-------------------+ |?MySQLMySQLMySQL???| +-------------------+
?
15、REVERSE(str)：將字符串str反轉(zhuǎn)后返回
mysql>?select?reverse('abcdef');+-------------------+ |?reverse('abcdef')?| +-------------------+ |?fedcba????????????| +-------------------+
?
16、CHAR(N,… [USING? charset_name])：將每個(gè)參數(shù)N解釋為整數(shù)（字符的編碼），并返回每個(gè)整數(shù)對(duì)應(yīng)的字符所構(gòu)成的字符串(NULL值被忽略)。
mysql>?select?char(77,121,83,81,'76'),char(77,77.3,'77.3');+-------------------------+----------------------+ |?char(77,121,83,81,'76')?|?char(77,77.3,'77.3')?| +-------------------------+----------------------+ |?MySQL???????????????????|?MMM??????????????????| +-------------------------+----------------------+
默認(rèn)情況下，函數(shù)返回二進(jìn)制字符串，若想返回針對(duì)特定字符集的字符串，使用using選項(xiàng)
mysql>?SELECT?charset(char(0x65)),?charset(char(0x65?USING?utf8));+---------------------+--------------------------------+ |?charset(char(0x65))?|?charset(char(0x65?USING?utf8))?| +---------------------+--------------------------------+ |?binary??????????????|?utf8???????????????????????????| +---------------------+--------------------------------+
?
17、FORMAT(X,D[,locale])：以格式‘#,###,###.##’格式化數(shù)字X
-
D指定小數(shù)位數(shù)
-
locale指定國(guó)家語(yǔ)言(默認(rèn)的locale為en_US)
mysql> SELECT format(12332.123456, 4),format(12332.2,0);+————————-+——————-+
| format(12332.123456, 4) | format(12332.2,0) |
+————————-+——————-+
| 12,332.1235 ? ? ? ? ? ? | 12,332 ? ? ? ? ? ?|
+————————-+——————-+mysql> SELECT format(12332.2,2,’de_DE’);+—————————+
| format(12332.2,2,’de_DE’) |
+—————————+
| 12.332,20 ? ? ? ? ? ? ? ? |
+—————————+
?
18、SPACE(N)：返回由N個(gè)空格構(gòu)成的字符串
mysql>?select?space(3);+----------+ |?space(3)?| +----------+ |??????????| +----------+
?
19、LEFT(str, len)：返回最左邊的len長(zhǎng)度的子串
mysql>?select?left('chinaitsoft',5);+-----------------------+ |?left('chinaitsoft',5)?| +-----------------------+ |?china?????????????????| +-----------------------+
?
20、RIGHT(str, len)：返回最右邊的len長(zhǎng)度的子串
mysql>?select?right('chinaitsoft',5);+------------------------+ |?right('chinaitsoft',5)?| +------------------------+ |?tsoft??????????????????| +------------------------+
?
21、STRCMP(expr1,expr2)：如果兩個(gè)字符串是一樣的則返回0；如果第一個(gè)小于第二個(gè)則返回-1；否則返回1
mysql>?select?strcmp('text','text');+-----------------------+ |?strcmp('text','text')?| +-----------------------+ |?????????????????????0?| +-----------------------+mysql>?SELECT?strcmp('text',?'text2'),strcmp('text2',?'text');+-------------------------+-------------------------+ |?strcmp('text',?'text2')?|?strcmp('text2',?'text')?| +-------------------------+-------------------------+ |??????????????????????-1?|???????????????????????1?| +-------------------------+-------------------------+
相關(guān)學(xué)習(xí)推薦：mysql視頻教程
.png)
