sql - sql 中的 concat() 如何添加两个或多个参数，包括特殊字符？

Question

select CONCAT(address,city) as Address from student order by Address desc;

这是一个查询，用于显示学生表中别名为地址的地址和城市

--- Program output ---

ADDRESS
----------------------------------------------------------------------
LMCCoimbatore
FFFVilupuram
BBBAgra
ABCSalem
AAAPondichery


--- Expected output (text)---
ADDRESS
------------------------------------------------------------------------
LMC, Coimbatore
FFF, Vilupuram
BBB, Agra
ABC, Salem
AAA, Pondichery

如何在城市和地址之间添加昏迷和空格？当我将第三个字符串用作“，”时，它给出了无效的参数数量错误。

Answer 1

CONCAT 版本:

我会推荐 CONCAT，因为它会保护您在其中一个参数为 null 时不返回 NULL - 至少在 MSSQL 中是这样。

MSSQL & MySQL & PostgresSQL & Teradata:

select CONCAT(address,', ',city) as Address from student order by Address desc;

MySQL 版本 2:

select CONCAT_WS(', ', address,city) as Address from student order by Address desc;

甲骨文、IBM DB2 和 IBM Informix:

select CONCAT(address,CONCAT(', ',city)) as Address from student order by Address desc;

IBM DB2:

select address CONCAT ', ' CONCAT city as Address from student order by Address desc;

非 CONCAT 版本:

||是 ANSI 标准，但并非所有系统都使用它。

MSSQL 和 Sybase IQ:

select address + ', ' + city as Address from student order by Address desc;

MySQL(所谓的邻近运算符，默认行为):

select address ', ' city as Address from student order by Address desc;

PostgresSQL & Oracle & MySQL(使用 SET sql_mode='PIPES_AS_CONCAT';)& IBM Informix & Sybase IQ:

select address || ', ' || city as Address from student order by Address desc;