SQL-Server CONCAT 案例

Question

我有以下练习:

concatenate first, middle, last name and name suffix to form the customer’s name in the following format: FirstName [MiddleName.] LastName[, Suffix]. Note that NULL values should be omitted.

我将此解释为以下场景(从图像创建表格并插入一些值):

请在下面找到我的示例数据，名称是#TB_Customer CustomerName 列是预期结果，格式应为

• FirstName MiddleName.LastName, Suffix 如果我有所有的输入田野。

• MiddleName 和 Suffix 可以是可选的，因此情况是:

• 如果有后缀但没有MiddleName，则CustomerName
  格式应为名字姓氏,后缀

• 如果有 MiddleName 但没有 后缀，则 CustomerName
  格式应为 FirstName MiddleName.LastName

• 如果 MiddleName 和 Suffix 均为 null，则 CustomerName 应该格式为 FirstName LastName)

这就是我得到的:

但是正如您所看到的，我编写的 CustomerName 案例查询并未按预期工作(请参阅上面的带有项目符号的 cases)

我为获取 CustomerName 列而编写的查询是:

SELECT 
(case
when (MiddleName is not null and Suffix is not null)  then
CONCAT(c.FIRSTNAME,' ', c.MiddleName,'.', c.LASTNAME, ', ',Suffix)  
when (MiddleName is null and suffix is null) then 
CONCAT(c.FIRSTNAME,' ' ,c.LASTNAME) 
when (MiddleName is null and Suffix is not null )then
concat (c.FirstName, ' ', c.LastName, ', ',Suffix )  
when (Suffix is null and MiddleName is not null) then
concat (c.FirstName, ' ',MiddleName,'.',LastName)
end 
)AS CustomerName
,c.*
FROM   #TB_Customer c;

我有 2 个问题:

• 我理解这个练习吗？我是否有良好的逻辑？
• 我在哪里犯了错误？正确的查询是什么？

使用 SQL-Server 2012

编辑

要重新创建我的场景，请参阅下面的代码(很抱歉没有链接 fiddle ，但网站在我当前的位置没有响应)

CREATE TABLE #TB_Customer
(
CustomerID int , --PK
Title varchar(50),
FirstName varchar(50),
MiddleName varchar(50),
LastName varchar(50),
Suffix varchar(50),
EmailAddress varchar(50),
Phone varchar(50),
Gender varchar(50),
Birthdate varchar(50),
--no fk
PRIMARY KEY (CustomerID)
)

insert into #TB_Customer values
('1','Sir','John','Jacob','Adams','St','johnJacobAdams@gmail.com','0677731235','M','1989-04-06'),
('2','Mr.','Russel','Thyrone','Peterson','pr','thyronePeterson@yahoo.com','555-010405','M','1963-02-01'),
('3','Ms.','Anne','Candice','Acola','aca','CandiceA@gmail.com','07408989989','F','1988-05-19'),
('4','Mrs.','Sophia','Veronika','Turner','tvs','SophiaVT@facebook.de','0423423887','F','1983-06-20'),
('5','Ms','Margaret','','Delafleur','','delaMarg@yahoo.co','233223355','Female','1982-02-25'),
('6','Mrs','Jessica','Luana','Cruz','','Jess@yahoo.com','787876631','Female','1922-05-05'),
('7','Mr','Dyrius','','Cruz','dc','dyr33@yahoo.com','0673332211','Male','1987-03-01')

update #TB_Customer
set Gender = 'Male' where Gender = 'M'
update #TB_Customer
set Gender = 'Female' where Gender = 'F'

Answer 1

像这样的东西应该也有效......

SELECT concat(firstname
     ,CASE WHEN ISNULL(middlename,'') <> '' THEN ' '+middlename+'.'
        WHEN ISNULL(middlename,'') <> '' AND ISNULL(suffix,'') = '' THEN '.' 
        ELSE ' ' END
     ,lastname
     ,CASE WHEN ISNULL(suffix,'') <> '' THEN ', '+suffix END)
FROM #TB_Customer

输出:

John Jacob.Adams, St
Russel Thyrone.Peterson, pr
Anne Candice.Acola, aca
Sophia Veronika.Turner, tvs
Margaret Delafleur
Jessica Luana.Cruz
Dyrius Cruz, dc
John Adams, St