blocks|key|4089342|text|您可以使用CASE表达式：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4089343|SELECT+MAX(CASE+WHEN+ISNULL(FirstName,+'')+=+''+AND
+++++++++++++++++++++ISNULL(MiddleName,+'')+=+''+AND
+++++++++++++++++++++ISNULL(LastName,+'')+=+''
++++++++++++++++THEN+'N/A'
++++++++++++++++ELSE+LTRIM(RTRIM((ISNULL(LastName,'')+%2B++',+'+%2B
++++++++++++++++++++++++++++++++++ISNULL(FirstName,'')+%2B+',+'+%2B
++++++++++++++++++++++++++++++++++ISNULL(MiddleName,''))))
+++++++++++END)+AS+FullName
FROM+yourTable
...|code-block|syntax|javascript|4089344|entityMap^0|5|4|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

You could use a <code>CASE</code> expression:

<pre><code>SELECT MAX(CASE WHEN ISNULL(FirstName, '') = '' AND
 ISNULL(MiddleName, '') = '' AND
 ISNULL(LastName, '') = ''
 THEN 'N/A'
 ELSE LTRIM(RTRIM((ISNULL(LastName,'') + ', ' +
 ISNULL(FirstName,'') + ', ' +
 ISNULL(MiddleName,''))))
 END) AS FullName
FROM yourTable
...
</code></pre>

blocks|key|4089338|text|像下面这样使用Concat，这将执行隐式转换。所以不需要使用ISNULL。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4089339|select+isnull(MAX(LTRIM(RTRIM((concat(LastName,+
++++',+'+,
++++FirstName,+
++++',+'+,
++++MiddleName,'')))))+,'n/a')AS+FullName+from+table|code-block|syntax|javascript|4089340|entityMap^0|7|6|U|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

Use <code>Concat</code> like below this will do implicit conversion. So no need to use <code>ISNULL</code>.

<pre><code>select isnull(MAX(LTRIM(RTRIM((concat(LastName, 
 ', ' ,
 FirstName, 
 ', ' ,
 MiddleName,''))))) ,'n/a')AS FullName from table
</code></pre>

blocks|key|4488308|text|使用COALESCE和CASE语句检查|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|4488309|Declare+@FirstName+VARCHAR(50)='',@MiddleName+VARCHAR(50),@LastName+VARCHAR(50)

SELECT+
CASE+WHEN+ISNULL(COALESCE(@FirstName,@MiddleName,@LastName),'')<>''+
THEN+ISNULL(@FirstName,'')%2B','+%2BISNULL(@MiddleName,'')%2B','%2BISNULL(@LastName,'')
ELSE+'N/A'+END+AS+FullName|code-block|syntax|javascript|4488310|entityMap^0|2|8|B|6|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

Check with COALESCE and then CASE Statement:

<pre><code>Declare @FirstName VARCHAR(50)='',@MiddleName VARCHAR(50),@LastName VARCHAR(50)

SELECT 
CASE WHEN ISNULL(COALESCE(@FirstName,@MiddleName,@LastName),'')&lt;&gt;'' 
THEN ISNULL(@FirstName,'')+',' +ISNULL(@MiddleName,'')+','+ISNULL(@LastName,'')
ELSE 'N/A' END AS FullName
</code></pre>

blocks|key|4089396|text|下面的方法可能看起来非常复杂，但它确实使添加或删除列变得更简单，尽管它的复杂性很高，但它实际上并没有在幕后做那么多工作，因此不会增加太多开销。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4089397|第一步是将每个列撤消为具有公共列名的行，这样您就可以转到|4089398|FirstName+++MiddleName++LastName++++
------------------------------------
A+++++++++++NULL++++++++C+++++++++++|code-block|syntax|javascript|4089399|转到|4089400|Name
------
A
NULL
C|4089401|使用CROSS+APPLY和表值构造函数VALUES|offset|length|style|CODE|4089402|SELECT++x.Name
FROM++++(VALUES+('A',+NULL,'C'))+AS+t+(FirstName,+MiddleName,+LastName)+
CROSS+APPLY+(VALUES+(1,+t.FirstName),+(2,+t.MiddleName),+(3,+t.LastName))+x+(SortOrder,+Name)
ORDER+BY+x.SortOrder|4089403|然后您可以使用WHERE+ISNULL(Name,+'')+<>+''删除NULL和空白，然后只有有效的数据连接在一起，您可以做using+SQL+Server's+XML+Extensions。因此，您将得到一个完整的查询，如：|4089404|WITH+TestData+AS
(+++SELECT++*
++++FROM+(VALUES+('A'),+(NULL))+AS+f+(FirstName)
++++CROSS+JOIN+(VALUES+('B'),+(NULL))+AS+m+(MiddleName)
++++CROSS+JOIN+(VALUES+('C'),+(NULL))+AS+l+(LastName)
)
SELECT++t.*,
++++++++NamesConcat+=+ISNULL(STUFF(NamesConcat.value('.',+'NVARCHAR(MAX)'),+1,+2,+''),+'N/A')
FROM++++TestData+AS+t
++++++++CROSS+APPLY
++++++++(+++SELECT++',+'+%2B+x.Name
++++++++++++FROM++++(VALUES+
++++++++++++++++++++++++(1,+t.FirstName),+
++++++++++++++++++++++++(2,+t.MiddleName),+
++++++++++++++++++++++++(3,+t.LastName)
++++++++++++++++++++)+x+(SortOrder,+Name)
++++++++++++WHERE+++ISNULL(x.Name,+'')+<>+''+--+NOT+BLANK+OR+NULL
++++++++++++ORDER+BY+x.SortOrder
++++++++++++FOR+XML+PATH(''),+TYPE
++++++++)+x+(NamesConcat);|4089405|结果|BOLD|4089406|FirstName+++MiddleName++LastName++++NamesConcat
-------------------------------------------------
A+++++++++++B+++++++++++C+++++++++++A,+B,+C
A+++++++++++NULL++++++++C+++++++++++A,+C
A+++++++++++B+++++++++++NULL++++++++A,+B
A+++++++++++NULL++++++++NULL++++++++A
NULL++++++++B+++++++++++C+++++++++++B,+C
NULL++++++++NULL++++++++C+++++++++++C
NULL++++++++B+++++++++++NULL++++++++B
NULL++++++++NULL++++++++NULL++++++++N/A|4089407|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/10381975/1048425^0|0|0|0|0|0|2|B|K|6|0|0|7|S|1S|X|0|0|0|0|2|0|0^^$0|@$1|2|3|4|5|6|7|1B|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1C|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1D|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1E|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|1F|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|1G|8|@$O|1H|P|1I|Q|R]|$O|1J|P|1K|Q|R]]|9|@]|A|$]]|$1|S|3|T|5|F|7|1L|8|@]|9|@]|A|$G|H]]|$1|U|3|V|5|6|7|1M|8|@$O|1N|P|1O|Q|R]]|9|@$O|1P|P|1Q|1|1R]]|A|$]]|$1|W|3|X|5|F|7|1S|8|@]|9|@]|A|$G|H]]|$1|Y|3|Z|5|6|7|1T|8|@$O|1U|P|1V|Q|10]]|9|@]|A|$]]|$1|11|3|12|5|F|7|1W|8|@]|9|@]|A|$G|H]]|$1|13|3|-4|5|6|7|1X|8|@]|9|@]|A|$]]]|14|$15|$5|16|17|18|A|$19|1A]]]]

The below method may seem quite complicated, but it does make adding or removing columns much simpler, and for all its perceived complexity it isn't actually doing that much under the hood, so doesn't add much overhead.

The first step is to unpivot each of your columns to rows with a common column name, so you would turn

<pre><code>FirstName MiddleName LastName 
------------------------------------
A NULL C 
</code></pre>

Into 

<pre><code>Name
------
A
NULL
C
</code></pre>

Using <code>CROSS APPLY</code> along with the table value constructor <code>VALUES</code>

<pre><code>SELECT x.Name
FROM (VALUES ('A', NULL,'C')) AS t (FirstName, MiddleName, LastName) 
CROSS APPLY (VALUES (1, t.FirstName), (2, t.MiddleName), (3, t.LastName)) x (SortOrder, Name)
ORDER BY x.SortOrder
</code></pre>

Then you can remove NULLs and blanks with <code>WHERE ISNULL(Name, '') &lt;&gt; ''</code>, then you only have valid data to concatenate together which you can do <a href="https://stackoverflow.com/a/10381975/1048425">using SQL Server's XML Extensions</a>. So you end up with a full query like:

<pre><code>WITH TestData AS
( SELECT *
 FROM (VALUES ('A'), (NULL)) AS f (FirstName)
 CROSS JOIN (VALUES ('B'), (NULL)) AS m (MiddleName)
 CROSS JOIN (VALUES ('C'), (NULL)) AS l (LastName)
)
SELECT t.*,
 NamesConcat = ISNULL(STUFF(NamesConcat.value('.', 'NVARCHAR(MAX)'), 1, 2, ''), 'N/A')
FROM TestData AS t
 CROSS APPLY
 ( SELECT ', ' + x.Name
 FROM (VALUES 
 (1, t.FirstName), 
 (2, t.MiddleName), 
 (3, t.LastName)
 ) x (SortOrder, Name)
 WHERE ISNULL(x.Name, '') &lt;&gt; '' -- NOT BLANK OR NULL
 ORDER BY x.SortOrder
 FOR XML PATH(''), TYPE
 ) x (NamesConcat);
</code></pre>

Result

<pre><code>FirstName MiddleName LastName NamesConcat
-------------------------------------------------
A B C A, B, C
A NULL C A, C
A B NULL A, B
A NULL NULL A
NULL B C B, C
NULL NULL C C
NULL B NULL B
NULL NULL NULL N/A
</code></pre>

blocks|key|2405062|text|从个人数据中选择Isnull(FirstName，')%2B‘%2B’，'%2B+Isnull(MiddleName，')+%2B+'%2B‘，'%2B+Isnull(Lastname，’')作为名称|type|ordered-list-item|depth|inlineStyleRanges|entityRanges|data|2405063|从个人数据中选择FirstName+%2B‘'+%2B’，'%2B+MiddleName+%2B‘’%2B，‘%2B+Lastname作为名称|2405064|注意:如果存在所有值，那么第二个查询将正常工作，如果任何人都为null，那么它将返回null作为名称，为了避免这种担心，请使用第一个查询。|unstyled|2405065|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|J|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|K|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|L|8|@]|9|@]|A|$]]]|H|$]]

<ol>
<li>select Isnull(FirstName,' ') +' '+ ','+ Isnull(MiddleName,' ')+' '+ ' ,'+ Isnull(Lastname,' ') as Name from personaldata</li>
<li>select FirstName +' '+','+ MiddleName +' '+',' + Lastname as Name from personaldata</li>
</ol>

Note: The Second query will work fine if all value present and if anyone is null then it will return null for Name, to avoid such kind of concern please use the first query.

I want to concatenate 3 columns in SQL server as below:

<pre><code>MAX(LTRIM(RTRIM((ISNULL(LastName,'') + 
 ', ' + 
 ISNULL(FirstName,'') + 
 ', ' + 
 ISNULL(MiddleName,''))))) AS FullName
</code></pre>

I have used value of this column in SELECT clause as:

<pre><code>MAX(FullName) AS FullName,
</code></pre>

I would like to handle NULL values, in case all 3 last name, middle name and first name are BLANK or NULL. The query used above will show " , , " in case all 3 columns are NULL or BLANK. But I want to show "N/A" in such case.

Thanks in advance.

Concatenate first name, last name and middle name with comma

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我希望将SQL server中的3列连接起来，如下所示：MAX(LTRIM(RTRIM((ISNULL(LastName,'') +     ', ' +     ISNULL(FirstName,'') +     ', ' +     ISNULL(MiddleName,''))))) AS FullName我在SELECT子句中使用了此列的值：MAX(FullName) AS FullNam

问连用逗号连接名、姓和中间名
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问连用逗号连接名、姓和中间名EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问连用逗号连接名、姓和中间名
EN