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                                           |
+------------------------------------------------+