logo标签列表

SQL-Server CONCAT case

sqlsql-servert-sqlsql-server-2012
6

我有以下练习:

将名字的第一部分、中间部分、姓和后缀连接起来，以以下格式形成客户名称：FirstName [MiddleName.] LastName[, Suffix]。请注意，应省略NULL值。

我理解这个问题的场景如下(从图像创建表并插入了一些值)：

请查看我的示例数据，名称为＃TB_Customer enter image description here CustomerName列是期望的结果，应该为以下格式

  • FirstName MiddleName.LastName, Suffix如果我有所有字段的输入。
  • 中间名和后缀可以选择性地省略，所以情况如下:
  • 如果存在suffix但不存在MiddleName，则CustomerName
    应该是Firstname LastName,Suffix的形式。
  • 如果存在MiddleName但不存在suffix，则CustomerName
    应该是FirstName MiddleName.LastName的形式。
  • 如果MiddleName和Suffix都为空，则CustomerName应该
    是FirstName LastName的形式。

这是我得到的结果: enter image description here

但是，如您所见，我编写的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;

我有两个问题：

  • 我是否正确理解了练习并且逻辑正确？
  • 我在哪里犯了错误，正确的查询是什么？

使用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'
1
@DanBracuk，请查看期望结果的第5行。那不太好。还有第7行。 - CM2K
2
当您不显示结果时，我们应该如何看出您的查询不起作用？此外，您列出了三种情况，但您的代码有四种，并且顺序相当奇怪。 - Henrik supports the community
1
@Henrik，这里有4种情况，第一种是每个字段都“非空”的情况。列的预期值在项目列表中。我会编辑并添加第四种情况的项目符号。 - CM2K
@Henrik，你说得对，这不是非常清楚，抱歉。我已经编辑过了，希望现在没问题了。 - CM2K
1
你意识到了吗？你正在检查 NULL，但是你的样本数据中根本没有一个 NULL。 - Sean Lange
显示剩余5条评论
4个回答
11

这样做也应该能够起作用...

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
标记为答案，因为提供了一个不使用 coalesce 的工作解决方案（尝试仅使用 case 完成，谢谢）。 - CM2K
@Doolius 当条件 WHEN ISNULL(middlename,'') <> '' AND ISNULL(suffix,'') = '' THEN '.' 中的 middlename 不为空，那么第一个 WHEN 条件将始终被执行。请问这个条件会被评估吗？ - Priyank Patel
5

我无法看到您查询语句中的错误，我知道如果其中一个列为空，则所有其他列都将为空，但请尝试以下方式：

SELECT COALESCE(c.FIRSTNAME,'') + ' ' +
        CASE WHEN COALESCE(c.MiddleName,'') = ''
                THEN ''
                ELSE c.MiddleName + '.'
            END
        + COALESCE(c.LASTNAME,'') +
        CASE WHEN COALESCE(Suffix,'') = ''
                THEN ''
                ELSE  ', ' + Suffix
             END AS CustomerName, c.*

FROM   #TB_Customer c;

@Henrik是正确的，“”和NULL是不同的东西

这里是第5行的代码（请参见第2张图片）：Margaret .Delafleur，应该返回Margaret Delafleur，不含句点或逗号。 - CM2K
已编辑，但图片显示Delafleur前有一个点。 - Tiago Oliveira de Freitas
这个很好用，谢谢。我接受了Doolius的答案，因为它没有使用coalesce函数，我只是在寻找case语句（但是这是一个很棒的解决方案，我点了赞）。 - CM2K
1
SELECT
    STUFF(RTRIM(
        CONCAT('  ',
            COALESCE(NULLIF(FirstName,'') + ' ', ''),
            COALESCE(NULLIF(MiddleName,'') + '.', ''),
            COALESCE(NULLIF(LastName,''), ''),
            COALESCE(', ' + NULLIF(Suffix,'') , '')
        )
    ), 2, 0,'')
FROM #TB_Customer tc

在某些奇怪的情况下，如果您只有后缀，添加STUFF

这个非常好用，谢谢。我接受了Doolius的答案，因为它没有使用COALESCE，而我只是在寻找情况（但这是一个很好的解决方案，我给他点赞）。 - CM2K
1
@CM2K 谢谢。你可能也想看看这个..如果你正在创建某种名称标签，它会以你实际想要看到的方式形成名称.. http://sqlfiddle.com/#!6/76d65/1 仅使用句点表示中间名字母等。 - JamieD77
1
现在你已经给出了代码，我可以看到错误在哪里：空字符串与NULL不同。
因此，您对是否存在中间名/后缀的测试将始终为真。
要么将这些字段设置为NULL，要么增加测试以检查NULL或空字符串。
谢谢您的回答，我不知道如何“检查 NULL 或空字符串”。有什么想法吗？ - CM2K
2
你可以使用"field IS NULL"来检查是否为NULL，使用"field =''"来检查是否为空字符串，并像你已经做的那样，结合AND或OR（可能还有一些括号）。 - Henrik supports the community
尝试过这样，不起作用： 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 or MiddleName = '') and (suffix is null or suffix = '') then CONCAT(c.FIRSTNAME,' ' ,c.LASTNAME) when (MiddleName is null or MiddleName = '' ) and (Suffix is not null )then concat (c.FirstName, ' ', c.LastName, ', ',Suffix ) when (Suffix is null or Suffix = '')and MiddleName is not null then concat (c.FirstName, ' ',MiddleName,'.',LastName) end )AS CustomerName ,c.* FROM #TB_Customer c; - CM2K
抱歉让它看起来有点混乱，但是在这里 fiddle 不起作用。我理解你的意思了吗？ - CM2K
这段代码真的很难读懂，但我认为它不会起作用。就我所看到的情况而言，当MiddleName和Suffix都为空字符串时，你将会进入第一个情况——但是在你添加了一个检查空字符串的条件后，它看起来是正确的。 - Henrik supports the community
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接