CONCAT()和CONCAT_WS()字符連接
mysql>?SELECT?CONCAT('mysql','5.6'); +-----------------------+ |?CONCAT('mysql','5.6')?| +-----------------------+ |?mysql5.6??????????????| +-----------------------+ 1?row?in?set?(0.09?sec) mysql>?SELECT?CONCAT('mysql','-','5.6'); +---------------------------+ |?CONCAT('mysql','-','5.6')?| +---------------------------+ |?mysql-5.6?????????????????| +---------------------------+ 1?row?in?set?(0.00?sec)
例如：將用戶的first_name和last_name連接起來成一個字符串
mysql>?SELECT?*?FROM?tdb_test; +----+------------+-----------+ |?id?|?first_name?|?last_name?| +----+------------+-----------+ |??1?|?A??????????|?B?????????| |??2?|?Jack???????|?Bob???????| |??3?|?tom%???????|?123???????| +----+------------+-----------+ 3?rows?in?set?(0.00?sec) mysql>?SELECT?CONCAT(first_name,last_name)?AS?fullname?FROM?tdb_test; +----------+ |?fullname?| +----------+ |?AB???????| |?JackBob??| |?tom%123??| +----------+ 3?rows?in?set?(0.00?sec)
CONCAT_WS()的第一個參數為分隔符，后面的為要連接的字符
mysql>?SELECT?CONCAT_WS('|','A','B','C'); +----------------------------+ |?CONCAT_WS('|','A','B','C')?| +----------------------------+ |?A|B|C??????????????????????| +----------------------------+ 1?row?in?set?(0.00?sec) mysql>?SELECT?CONCAT_WS('-','mysql','5.6'); +------------------------------+ |?CONCAT_WS('-','mysql','5.6')?| +------------------------------+ |?mysql-5.6????????????????????| +------------------------------+ 1?row?in?set?(0.00?sec)
FORMAT()數字格式化
mysql>?SELECT?FORMAT(12560.75,1); +--------------------+ |?FORMAT(12560.75,1)?| +--------------------+ |?12,560.8???????????| +--------------------+ 1?row?in?set?(0.01?sec) LOWER()和?UPPER() mysql>?SELECT?LOWER('MySql'); +----------------+ |?LOWER('MySql')?| +----------------+ |?mysql??????????| +----------------+ 1?row?in?set?(0.00?sec) mysql>?SELECT?UPPER('MySql'); +----------------+ |?UPPER('MySql')?| +----------------+ |?MYSQL??????????| +----------------+ 1?row?in?set?(0.00?sec)
LEFT()和RIGHT()
分別有兩個參數，第一個為所用的字符串，第二個為取幾位
例如獲取MySQL的前兩位
mysql>?SELECT?LEFT('MySQL',2); +-----------------+ |?LEFT('MySQL',2)?| +-----------------+ |?My??????????????| +-----------------+ 1?row?in?set?(0.00?sec)
例如獲取MySQL的后兩位
mysql>?SELECT?RIGHT('MySQL',2); +------------------+ |?RIGHT('MySQL',2)?| +------------------+ |?QL???????????????| +------------------+ 1?row?in?set?(0.00?sec)
LENGTH獲取字符串長度
mysql>?SELECT?LENGTH('MySQL'); +-----------------+ |?LENGTH('MySQL')?| +-----------------+ |???????????????5?| +-----------------+ 1?row?in?set?(0.02?sec)
LTRIM,RTRIM,TRIM
比如直接用SELECT TRIM(‘ mysql ‘);
將會刪除mysql的前和后的空格，LTRIM只刪除左側的空格，RTRIM只刪除右側的空格。
其他用法：利用TRIM()刪除左右兩側的特定字符。
例如：
mysql>?SELECT?TRIM(LEADING?'?'?FROM?'??MySQL???'); +-------------------------------------+ |?TRIM(LEADING?'?'?FROM?'??MySQL???')?| +-------------------------------------+ |?MySQL???????????????????????????????| +-------------------------------------+ 1?row?in?set?(0.02?sec) mysql>?SELECT?TRIM(TRAILING?'?'?FROM?'??MySQL???'); +--------------------------------------+ |?TRIM(TRAILING?'?'?FROM?'??MySQL???')?| +--------------------------------------+ |???MySQL??????????????????????????????| +--------------------------------------+ 1?row?in?set?(0.00?sec) mysql>?SELECT?TRIM(BOTH?'?'?FROM?'??MySQL???'); +----------------------------------+ |?TRIM(BOTH?'?'?FROM?'??MySQL???')?| +----------------------------------+ |?MySQL????????????????????????????| +----------------------------------+ 1?row?in?set?(0.01?sec)
注：TRIM()只能刪除前導和后續的，不能刪除字符中間的，比如TRIM(‘My SQL’);這個空格就刪不掉。
REPLACE()字符串替換
例如將??My??SQL???中的？替換成空
mysql>?SELECT?REPLACE('??My??SQL???','?',''); +--------------------------------+ |?REPLACE('??My??SQL???','?','')?| +--------------------------------+ |?MySQL??????????????????????????| +--------------------------------+ 1?row?in?set?(0.00?sec)
SUBSTRING()字符串截取
所含參數，從第幾位截，截取幾位。
mysql>?SELECT?SUBSTRING('MySQL',1,2); +------------------------+ |?SUBSTRING('MySQL',1,2)?| +------------------------+ |?My?????????????????????| +------------------------+ 1?row?in?set?(0.00?sec)
需要注意的是，和編程語言不同的是，第一位是1，不是0。
如果只有從第幾位起，沒有截取幾個，就會，一直截到最后。
mysql>?SELECT?SUBSTRING('MySQL',3); +----------------------+ |?SUBSTRING('MySQL',3)?| +----------------------+ |?SQL??????????????????| +----------------------+ 1?row?in?set?(0.00?sec)
位置也可以為負值（倒著數）
mysql>?SELECT?SUBSTRING('MySQL',-1); +-----------------------+ |?SUBSTRING('MySQL',-1)?| +-----------------------+ |?L?????????????????????| +-----------------------+ 1?row?in?set?(0.04?sec)
注，只能是起始位置可以有負值，但是截取長度不能有負值。
LIKE模式匹配（常用于查詢中）
mysql>?SELECT?'MySQL'?LIKE?'M%'; +-------------------+ |?'MySQL'?LIKE?'M%'?| +-------------------+ |?????????????????1?| +-------------------+ 1?row?in?set?(0.00?sec)
%是指任意一位或者多位
mysql>?SELECT?*?FROM?tdb_test; +----+------------+-----------+ |?id?|?first_name?|?last_name?| +----+------------+-----------+ |??1?|?A??????????|?B?????????| |??2?|?Jack???????|?Bob???????| |??3?|?tom%???????|?123???????| +----+------------+-----------+ 3?rows?in?set?(0.00?sec)
例如查詢，姓名中包含o的用戶
mysql>?SELECT?*?FROM?tdb_test?WHERE?first_name?LIKE?'%o%'; +----+------------+-----------+ |?id?|?first_name?|?last_name?| +----+------------+-----------+ |??3?|?tom%???????|?123???????| +----+------------+-----------+ 1?row?in?set?(0.00?sec)
例如查詢，姓名中包含%的用戶
mysql>?SELECT?*?FROM?tdb_test?WHERE?first_name?LIKE?'%%%'; +----+------------+-----------+ |?id?|?first_name?|?last_name?| +----+------------+-----------+ |??1?|?A??????????|?B?????????| |??2?|?Jack???????|?Bob???????| |??3?|?tom%???????|?123???????| +----+------------+-----------+ 3?rows?in?set?(0.00?sec)
發現顯示的是所有查詢結果。因為mysql會認為上述的%都為通配符。正確操作如下
mysql>?SELECT?*?FROM?tdb_test?WHERE?first_name?LIKE?'%1%%'?ESCAPE?'1'; +----+------------+-----------+ |?id?|?first_name?|?last_name?| +----+------------+-----------+ |??3?|?tom%???????|?123???????| +----+------------+-----------+ 1?row?in?set?(0.00?sec)
注：%：任意個字符.
_:任意一個字符。
?以上就是MySQL字符函數的詳細介紹的內容，更多相關內容請關注PHP中文網（www.php.cn）！
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
