在 T-SQL 中结合名字、中间名首字母、姓氏和后缀（无多余空格）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2699833/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Combine First, Middle Initial, Last name and Suffix in T-SQL (No extra spaces)
提问by Paul
I'm trying not to reinvent the wheel here...I have these four fields:
我尽量不在这里重新发明轮子......我有这四个领域：
[tbl_Contacts].[FirstName],
[tbl_Contacts].[MiddleInitial],
[tbl_Contacts].[LastName],
[tbl_Contacts].[Suffix]
And I want to create a FullNamefield in a view, but I can't have extra spaces if fields are blank...
我想FullName在视图中创建一个字段，但如果字段为空，我不能有额外的空格...
So I can't do FirstName + ' ' + MiddleInitial + ' ' + LastName + ' ' + Suffix...Because if there is no middle initial or suffix I'd have 2 extra spaces in the field. I think I need a Case statement, but I thought someone would have a handy method for this...Also, the middleinitialand suffixmay be null.
所以我不能这样做，FirstName + ' ' + MiddleInitial + ' ' + LastName + ' ' + Suffix...因为如果没有中间名首字母或后缀，我会在该字段中多出 2 个空格。我想我需要一个 Case 语句，但我认为有人会有一个方便的方法......此外，middleinitial和suffix可能为空。
回答by Thomas
Assuming that all columns could be nullable, you can do something like:
假设所有列都可以为空，您可以执行以下操作：
RTrim(Coalesce(FirstName + ' ','')
+ Coalesce(MiddleInitial + ' ', '')
+ Coalesce(LastName + ' ', '')
+ Coalesce(Suffix, ''))
This relies on the fact that adding to a NULL value yields a NULL.
这取决于添加到 NULL 值会产生 NULL 的事实。
回答by marc_s
Whichever options you choose, here's something to think about: this will be a rather involved and thus time consuming option, especially if you have it in a view which gets evaluated each and every time for each and every row in question.
无论您选择哪种选项，都需要考虑一下：这将是一个相当复杂且因此耗时的选项，尤其是如果您将它放在一个视图中，并且每次都会为所讨论的每一行进行评估。
If you need this frequently, I would recommend you add this to your base table as a persisted, computed field - something like:
如果您经常需要它，我建议您将其作为持久的计算字段添加到基表中 - 类似于：
ALTER TABLE dbo.tbl_Contacts
ADD FullName AS (insert the statement of your choice here) PERSISTED
When it's persisted, it becomes part of the underlying table, and it's stored and kept up to date by SQL Server. When you query it, you get back the current value withoutincurring the cost of having to concatenate together the fields and determine which to use and which to ignore...
当它被持久化时，它成为基础表的一部分，并由 SQL Server 存储并保持最新。当您查询它时，您可以返回当前值，而不会产生必须将字段连接在一起并确定使用哪些字段以及忽略哪些字段的成本...
Just something to think about - something that too many DBA's and database devs tend to ignore and/or not know about....
只是需要考虑的事情 - 太多 DBA 和数据库开发人员倾向于忽略和/或不知道的事情......
回答by Daniel Vassallo
You may want to pass the FirstName + ' ' + MiddleInitial + ' ' + LastName + ' ' + Suffixconcatenation through the REPLACE()function in order to substitute duplicate spaces into a single space.
您可能希望FirstName + ' ' + MiddleInitial + ' ' + LastName + ' ' + Suffix通过REPLACE()函数传递串联，以便将重复的空格替换为单个空格。
REPLACE(FirstName + ' ' + MiddleInitial + ' ' + LastName + ' ' + Suffix, ' ', ' ')
-- -- -
EDIT:
编辑：
Just noticed that some of your fields may be NULL, and therefore the above would not work in that case, as the whole string would become NULL. In this case, you can use the COALESCE()method as suggested by Thomas, but still wrapped it in a REPLACE():
只是注意到您的某些字段可能是NULL，因此在这种情况下上述内容不起作用，因为整个字符串将变为NULL. 在这种情况下，您可以使用COALESCE()Thomas 建议的方法，但仍将其包装在一个REPLACE():
REPLACE(RTRIM(COALESCE(FirstName + ' ', '') +
COALESCE(MiddleInitial + ' ', '') +
COALESCE(LastName + ' ', '') +
COALESCE(Suffix, '')), ' ', ' ')
Test:
测试：
SELECT REPLACE(RTRIM(COALESCE('John' + ' ', '') +
COALESCE('' + ' ', '') +
COALESCE('Doe' + ' ', '') +
COALESCE(NULL, '')), ' ', ' ')
-- Returns: John Doe
回答by Vijred
I had to join Firstname, Middlename, and Lastname. my challenge was to handle NULL values, used following code.
我必须加入名字、中间名和姓氏。我的挑战是处理 NULL 值，使用以下代码。
RTRIM(LTRIM(RTRIM(isnull(@firstname,'') + ' ' + isnull(@middlename,'')) + ' ' + isnull(@lastname,'')))
Test different scenarios if you are interested :)
如果您有兴趣，请测试不同的场景:)
DECLARE @firstname VARCHAR(MAX)
DECLARE @middlename VARCHAR(MAX)
DECLARE @lastname VARCHAR(MAX)
set @firstname = 'FirstName'
set @middlename = NULL
set @lastname = 'LastName'
SELECT '|'+RTRIM(LTRIM(RTRIM(isnull(@firstname,'') + ' ' + isnull(@middlename,'')) + ' ' + isnull(@lastname,'')))+'|'
--
回答by Lukasz Szozda
If you are using SQL Server 2012+you could use CONCATand +:
如果您正在使用，SQL Server 2012+您可以使用CONCAT和+：
SELECT RTRIM(
CONCAT(FirstName + ' ', MiddleInitial + ' ', LastName + ' ', Suffix)
) AS [FullName]
FROM tbl_Contacts;
How it works:
这个怎么运作：
- If any part of full name is
NULLthenNULL + ' '→NULL CONCAThandlesNULL- In case that after part of name there are only
NULLs,TRIMlast space.
- 如果全名的任何部分
NULL，然后NULL + ' '→NULL CONCAT把手NULL- 如果在名称的一部分之后只有
NULLs,TRIM最后一个空格。
回答by Harikumar
select CONCAT(IFNULL(FirstName, ''), '', IFNULL(MiddleName, ''), '', IFNULL(LastName, '')) AS name from table
select CONCAT(IFNULL(FirstName, ''), '', IFNULL(MiddleName, ''), '', IFNULL(LastName, '')) AS name from table
回答by Jonathan
Here is a solution:
这是一个解决方案：
CREATE FUNCTION dbo.udf_IsNullOrEmpty
(
@vchCheckValue VARCHAR(MAX)
,@vchTrueValue VARCHAR(MAX)
,@vchFalseValue VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN CASE WHEN NULLIF(RTRIM(LTRIM(@vchCheckValue)),'') IS NULL THEN @vchTrueValue ELSE @vchFalseValue END
END
SELECT FirstName + ' ' +
dbo.udf_IsNullOrEmpty(MiddleInitial,'',MiddleInitial + ' ') +
LastName +
dbo.udf_IsNullOrEmpty(Suffix,'',' ' + Suffix)
FROM tbl_Contacts
回答by Akshay Kumar Konduri
create function getfname(@n varchar(30)) returns varchar(30) as begin declare @s varchar(30) set @s=LEFT(@n,charindex(' ',@n)-1) return @s end
create function getfname(@n varchar(30)) 返回 varchar(30) as begin declare @s varchar(30) set @s=LEFT(@n,charindex(' ',@n)-1) return @s end
create function getLname(@n varchar(30)) returns varchar(30) as begin declare @s varchar(30)
create function getLname(@n varchar(30)) 返回 varchar(30) as begin declare @s varchar(30)
set @s=substring(@n,charindex(' ',@n+1),Len(@n))
set @s=substring(@n,charindex(' ',@n+1),Len(@n))
return @s end
返回@s 结束
回答by Bishop Chakraborty
the query:
查询：
SELECT retire.employeehrmsid,
Isnull(retire.firstname, '') + ' '
+ Isnull(retire.middlename, '') + ' '
+ Isnull(retire.lastname, '') AS FullName,
retire.dojtoservice,
retire.designation,
emphistory.currentdoj,
emphistory.presentddo,
emphistory.office,
transfer.generatetid AS TransferID,
transfer.transferdate,
transfer.currentlocation,
transfer.newlocation,
transfer.datas AS Transfer_Doc,
release.generaterid AS ReleaseID,
release.releasedate,
release.datar AS Release_Doc,
employeeserviceupdate.dataeu AS Join_Doc
FROM retire
INNER JOIN emphistory
ON retire.id = emphistory.id
INNER JOIN employeeserviceupdate
ON retire.id = employeeserviceupdate.id
INNER JOIN transfer
ON retire.id = transfer.id
AND emphistory.ehrid = transfer.ehrid
INNER JOIN release
ON transfer.tid = release.tid
