MySQL CONCAT_WS方法用法及代碼示例

例子

考慮下表有關一些學生的信息：

可以使用此處的代碼創建上述示例表。

基本用法

要將 fname 和 lname 連接起來，並在其間添加 '*'：

SELECT CONCAT_WS('*', fname, lname)
FROM students;



+------------------------------+
| CONCAT_WS('*', fname, lname) |
+------------------------------+
| Sky*Towner                   |
| Ben*Davis                    |
| Travis*Apple                 |
| Arthur*David                 |
| Benjamin*Town                |
+------------------------------+

要使用 AS 為結果列提供別名：

SELECT CONCAT_WS(' ', fname, lname) AS "Full Name"
FROM students;



+---------------+
| Full Name     |
+---------------+
| Sky Towner    |
| Ben Davis     |
| Travis Apple  |
| Arthur David  |
| Benjamin Town |
+---------------+

數字參數

數字參數自動轉換為字符串：

SELECT CONCAT_WS(' ', fname, 'is', age, 'years old.') AS "Sentence"
FROM students;



+---------------------------+
| Sentence                  |
+---------------------------+
| Sky is 17 years old.      |
| Ben is 19 years old.      |
| Travis is 18 years old.   |
| Arthur is 16 years old.   |
| Benjamin is 17 years old. |
+---------------------------+

空參數

NULL 輸入字符串被跳過：

SELECT CONCAT_WS('-', 'Welcome', NULL, 'to', 'SkyTowner!');



+-----------------------------------------------------+
| CONCAT_WS('-', 'Welcome', NULL, 'to', 'SkyTowner!') |
+-----------------------------------------------------+
| Welcome-to-SkyTowner!                               |
+-----------------------------------------------------+

NULL 分隔符返回 NULL ：

SELECT CONCAT_WS(NULL, 'Welcome', 'to', 'SkyTowner!');



+------------------------------------------------+
| CONCAT_WS(NULL, 'Welcome', 'to', 'SkyTowner!') |
+------------------------------------------------+
| NULL                                           |
+------------------------------------------------+