MySQL字元函數

本文以執行個體形式來講解MySQL的函數

按照功能的分類

1 字元函數

2 數值運算符與函數

3 比較運算符與函數

4 日期時間函數

5 資訊函數

6 聚合函數

7 加密函數

字元函數分類

CONCAT()                 字元連接配接

CONCAT_WS()          使用指定的分隔符進行連接配接

FORMAT()                  數字格式化

LOWER()                    轉換成小寫字母

UPPER()                     轉換成大寫字母

LEFT()                        擷取左側字元

RIGHT()                      擷取右側字元

字元函數分類

CONCAT()                 字元連接配接

CONCAT_WS()          使用指定的分隔符進行連接配接

FORMAT()                  數字格式化

LOWER()                    轉換成小寫字母

UPPER()                     轉換成大寫字母

LEFT()                        擷取左側字元

RIGHT()                      擷取右側字元

CONCAT()字元連接配接，資料表的名字後面可以加東西

例1

mysql> SELECT CONCAT('provinces','--');

+--------------------------+

| CONCAT('provinces','--') |

+--------------------------+

| provinces--              |

+--------------------------+

1 row in set (0.00 sec)

建立一張示範表

mysql> CREATE TABLE testwish(

    -> first_name VARCHAR(20) DEFAULT NULL,

    -> last_name VARCHAR(10)  DEFAULT NULL

    -> );

Query OK, 0 rows affected (0.01 sec)

mysql> SHOW COLUMNS FROM testwish;

+------------+-------------+------+-----+---------+-------+

| Field      | Type        | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| first_name | varchar(20) | YES  |     | NULL    |       |

| last_name  | varchar(10) | YES  |     | NULL    |       |

+------------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

插入一些記錄

mysql> INSERT testwish(first_name,last_name) VALUES('QQ','121'),('AC','BD'),('121','725');

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM testwish;

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| QQ         | 121       |

| AC         | BD        |

| 121        | 725       |

+------------+-----------+

3 rows in set (0.00 sec)

例2

把這兩個字段合并在一起輸出，用AS起個别名

mysql> SELECT CONCAT(first_name,last_name) AS  fullname FROM testwish;

+----------+

| fullname |

+----------+

| QQ121    |

| ACBD     |

| 121725   |

+----------+

3 rows in set (0.00 sec)

CONCAT_WS()需要至少三個參數

例3，字元連接配接

mysql> SELECT CONCAT_WS('|','A','B','C');

+----------------------------+

| CONCAT_WS('|','A','B','C') |

+----------------------------+

| A|B|C                      |

+----------------------------+

1 row in set (0.00 sec)

mysql>

mysql> SELECT CONCAT_WS('-','test1','test2');

+--------------------------------+

| CONCAT_WS('-','test1','test2') |

+--------------------------------+

| test1-test2                    |

+--------------------------------+

1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS('-','test1','test2','test');

+---------------------------------------+

| CONCAT_WS('-','test1','test2','test') |

+---------------------------------------+

| test1-test2-test                      |

+---------------------------------------+

1 row in set (0.00 sec)

例4

截取小數點後兩位數字

mysql> SELECT FORMAT(12580.45632,2);

+-----------------------+

| FORMAT(12580.45632,2) |

+-----------------------+

| 12,580.46             |

+-----------------------+

1 row in set (0.00 sec)

例5

大寫轉換成小寫

mysql> SELECT LOWER('MySQL');

+----------------+

| LOWER('MySQL') |

+----------------+

| mysql          |

+----------------+

1 row in set (0.00 sec)

例6

小寫轉換成大寫

mysql> SELECT UPPER('mysql');

+----------------+

| UPPER('mysql') |

+----------------+

| MYSQL          |

+----------------+

1 row in set (0.00 sec)

例7

擷取左側N個字元

mysql> SELECT LEFT('mysql',2);

+-----------------+

| LEFT('mysql',2) |

+-----------------+

| my              |

+-----------------+

1 row in set (0.00 sec)

例8 擷取左側N個字元，并轉換為小寫。用嵌套

mysql> SELECT LOWER(LEFT('MySQL',2));

+------------------------+

| LOWER(LEFT('MySQL',2)) |

+------------------------+

| my                     |

+------------------------+

1 row in set (0.00 sec)

例9

 擷取右側 N個字元

mysql> SELECT RIGHT('MySQL',2);

+------------------+

| RIGHT('MySQL',2) |

+------------------+

| QL               |

+------------------+

1 row in set (0.00 sec)

字元函數

LENGTH()              擷取字元串長度

LTRIM()                  删除前導空格

RTRIM()                 删除後續空格 

TRIM()                   删除前導和後續空格

SUBSTRING()       字元串截取

[NOT]LIKE             模式比對

PERLACE()          字元串替換

例10 擷取字元串長度

mysql> SELECT LENGTH('MySQL');

+-----------------+

| LENGTH('MySQL') |

+-----------------+

|               5 |

+-----------------+

1 row in set (0.00 sec)

注意如果有空格，也會算在裡面

mysql> SELECT LENGTH('  My  SQL');

+---------------------+

| LENGTH('  My  SQL') |

+---------------------+

|                   9 |

+---------------------+

1 row in set (0.00 sec)

前導空格，是第一個字元之前 的空格，後續空格，是最後一個字元之後的空格

例11，删除前導空格，并檢視長度

mysql> SELECT LENGTH ('  MySQL    ');

+------------------------+

| LENGTH ('  MySQL    ') |

+------------------------+

|                     11 |

+------------------------+

1 row in set (0.00 sec)

mysql> SELECT LENGTH (LTRIM('  MySQL    '));

+-------------------------------+

| LENGTH (LTRIM('  MySQL    ')) |

+-------------------------------+

|                             9 |

+-------------------------------+

1 row in set (0.00 sec)

例12，删除 後續空格，并檢視長度

mysql> SELECT LENGTH (RTRIM('  MySQL    '));

+-------------------------------+

| LENGTH (RTRIM('  MySQL    ')) |

+-------------------------------+

|                             7 |

+-------------------------------+

1 row in set (0.00 sec)

例13 去掉所有空格，并檢視長度

mysql> SELECT LENGTH (TRIM('  MySQL    '));

+------------------------------+

| LENGTH (TRIM('  MySQL    ')) |

+------------------------------+

|                            5 |

+------------------------------+

1 row in set (0.00 sec)

例14 删除前導中的“?”

mysql> SELECT TRIM(LEADING '?' FROM '??MySQL???');

+-------------------------------------+

| TRIM(LEADING '?' FROM '??MySQL???') |

+-------------------------------------+

| MySQL???                            |

+-------------------------------------+

1 row in set (0.00 sec)

例15 删除後續中的”?”

mysql> SELECT TRIM(TRAILING '?' FROM '??MySQL???');

+--------------------------------------+

| TRIM(TRAILING '?' FROM '??MySQL???') |

+--------------------------------------+

| ??MySQL                              |

+--------------------------------------+

1 row in set (0.00 sec)

 例16 删除所有的”?”

mysql> SELECT TRIM(BOTH '?' FROM '??MySQL???');

+----------------------------------+

| TRIM(BOTH '?' FROM '??MySQL???') |

+----------------------------------+

| MySQL                            |

+----------------------------------+

1 row in set (0.00 sec)

注意BOTH 删除不掉中間的”?”

mysql> SELECT TRIM(BOTH '?' FROM '??My??SQL???');

+------------------------------------+

| TRIM(BOTH '?' FROM '??My??SQL???') |

+------------------------------------+

| My??SQL                            |

+------------------------------------+

1 row in set (0.00 sec)

例17用字元串替換掉字元串

mysql> SELECT REPLACE('??My??SQL????','?','--');

+-----------------------------------+

| REPLACE('??My??SQL????','?','--') |

+-----------------------------------+

| ----My----SQL--------             |

+-----------------------------------+

1 row in set (0.00 sec)

mysql> SELECT REPLACE('??My??SQL????','?','');

+---------------------------------+

| REPLACE('??My??SQL????','?','') |

+---------------------------------+

| MySQL                           |

+---------------------------------+

1 row in set (0.00 sec)

例18 用字元串替換掉字元串,一對多

mysql> SELECT REPLACE('??My??SQL????','?','-+-!!');

+-----------------------------------------------+

| REPLACE('??My??SQL????','?','-+-!!')          |

+-----------------------------------------------+

| -+-!!-+-!!My-+-!!-+-!!SQL-+-!!-+-!!-+-!!-+-!! |

+-----------------------------------------------+

1 row in set (0.00 sec)

例19用字元串替換掉字元串，多對一

mysql> SELECT REPLACE('??My??SQL????','??','~');

+-----------------------------------+

| REPLACE('??My??SQL????','??','~') |

+-----------------------------------+

| ~My~SQL~~                         |

+-----------------------------------+

1 row in set (0.00 sec)

例20，字元串截取，從第一位開始，截取兩個

mysql> SELECT SUBSTRING('MySQL',1,2);

+------------------------+

| SUBSTRING('MySQL',1,2) |

+------------------------+

| My                     |

+------------------------+

1 row in set (0.00 sec)

注意不指定位置的情況

mysql> SELECT SUBSTRING('MySQL',3);

+----------------------+

| SUBSTRING('MySQL',3) |

+----------------------+

| SQL                  |

+----------------------+

1 row in set (0.00 sec)

例21 倒序截取

mysql> SELECT SUBSTRING('MySQL',-2);

+-----------------------+

| SUBSTRING('MySQL',-2) |

+-----------------------+

| QL                    |

+-----------------------+

1 row in set (0.00 sec)

模式的比對,%表示0個或多個字元，‘1’表示TRUE 

mysql> SELECT 'MySQL' LIKE 'M%';

+-------------------+

| 'MySQL' LIKE 'M%' |

+-------------------+

|                 1 |

+-------------------+

1 row in set (0.00 sec)

例22  查找第一個字元組中包含‘Q’的使用者 

mysql> SELECT * FROM testwish WHERE first_name LIKE '%Q%';

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| QQ         | 121       |

+------------+-----------+

1 row in set (0.00 sec)

目前表

mysql> SELECT *  FROM testwish;

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| QQ         | 121       |

| AC         | BD        |

| 121        | 725       |

| tom%       | 2016      |

+------------+-----------+

例子23查找 包含% 的使用者

告訴字元串，1 後面的不需要再進行通配符解析，直接預設為是标準的%

mysql> SELECT * FROM testwish WHERE first_name LIKE '%1%%' ESCAPE '1';

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| tom%       | 2016      |

+------------+-----------+