#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）！
