什么是连接可能包含空值的人名的最短TSQL

3 fields: FirstName, MiddleName, LastName

3个字段:FirstName,MiddleName,LastName

Any field can be null, but I don't want extra spaces. Format should be "First Middle Last", "First Last", "Last", etc.

任何字段都可以为null,但我不想要额外的空格。格式应为“First Middle Last”,“First Last”,“Last”等。

10 个解决方案

#1

use a UDF:

使用UDF:

`Select udfConcatName(First, Middle, Last) from foo`

That way all your logic for concatenating names is in one place and once you've gotten it written it's short to call.

这样你所有用于连接名字的逻辑都集中在一个地方,一旦你写完它就很难调用。

#2

    LTRIM(RTRIM(
    LTRIM(RTRIM(ISNULL(FirstName, ''))) + ' ' + 
    LTRIM(RTRIM(ISNULL(MiddleName, ''))) + ' ' + 
    LTRIM(ISNULL(LastName, ''))
    ))

NOTE: This won't leave trailing or leading spaces. That's why it's a little bit uglier than other solutions.

注意:这不会留下尾随或前导空格。这就是为什么它比其他解决方案有点丑陋。

#3

Assuming by "extra spaces", you mean extra spaces inserted during the concatenation (which is a reasonable assumption, I think. If you have extra spaces in your data, you should clean it up):

假设“额外的空格”,你的意思是在连接期间插入额外的空格(这是一个合理的假设,我认为。如果你的数据中有额外的空格,你应该清理它):

ISNULL(FirstName + ' ', '')  + ISNULL(MiddleName + ' ', '') + ISNULL(LastName, '')

works, since you'll add a space to the name - which if it's NULL yields NULL - which yields empty string.

工作,因为您将为名称添加一个空格 - 如果它为NULL,则产生NULL - 这会产生空字符串。

Edit: If you don't count the SET OPTION - which can be a connection or db option:

编辑:如果您不计算SET OPTION - 可以是连接或db选项:

SET CONCAT_NULL_YIELDS_NULL OFF
LTRIM(FirstName + ' ' + NULLIF(MiddleName + ' ', ' ') + LastName)

is a tiny bit shorter, but a large bit uglier.

有点短,但有点丑陋。

Edit2: Since you accepted the UDF answer - IMO, that's a bit of a cheat - here's some in the same vein:

编辑2:既然你接受了UDF的答案 - IMO,这有点像作弊 - 这里有一些相同的东西:

SELECT a FROM b

b is a view. ;) Or. a stored proc,

b是一个视图。 ;) 要么。存储过程,

EXEC c

But, since EXEC is optional:

但是,由于EXEC是可选的:

#4

LTRIM(RTRIM(ISNULL(FirstName, '') + ' ' + LTRIM(ISNULL(MiddleName, '') + ' ' + 
    ISNULL(LastName, ''))))

#5

Why not use a computed column on the table that performs the concat for you using your preferred syntax from the many posted here? Then you will just query the computed column - very elegant and if you persist the computed column then you may even get slight performance increase. Example here

为什么不使用您在此处发布的许多首选语法为您执行concat的表上使用计算列?然后你将只查询计算列 - 非常优雅,如果你坚持计算列,那么你甚至可能会略微提高性能。这里的例子

#6

replace(ltrim(rtrim(isnull(FirstName, '') + ' ' + isnull(MiddleName, '') + ' ' + isnull(LastName, ''))), '  ', ' ')

#7

'"' + ltrim(rtrim(isnull(FirstName,''))) + ' ' + ltrim(rtrim(isnull(MiddleName,''))) + 
' ' + ltrim(rtrim(isnull(LastName,''))) + '","' + ltrim(rtrim(isnull(FirstName,''))) + 
' ' + ltrim(rtrim(isnull(LastName,''))) + '","' + ltrim(rtrim(isnull(LastName,''))) + 
'"'

ETC

#8

DECLARE @first varchar(10) = 'First'
DECLARE @middle varchar(10) = ''
DECLARE @last varchar(10) = 'Last'

LTRIM(RTRIM(
    @first
    + ISNULL(NULLIF(' '+LTRIM(RTRIM(@middle)),' '),'')
    + ISNULL(NULLIF(' '+LTRIM(RTRIM(@last)),' '),'')
))

WHY THIS WORKS

为什么这样做

The fields are reduced to an empty string if NULL or whitespace by the LTRIM, RTRIM, and ISNULL functions.

如果LTRIM,RTRIM和ISNULL函数为NULL或空格,则字段将缩减为空字符串。

LTRIM(RTRIM(ISNULL(@middle,''))) -- Result is a trimmed non-null string value.

That value is prefixed with a single space, then compared to a single space by the NULLIF function. If equal, a NULL results. If not equal, then the value is used.

该值以单个空格为前缀,然后通过NULLIF函数与单个空格进行比较。如果相等,则结果为NULL。如果不相等,则使用该值。

NULLIF(' '+'',' ')       -- this would return NULL
NULLIF(' '+'Smith',' ')  -- this would return ' Smith'

Finally, ISNULL() is used to convert the NULL passed by NULLIF to an empty string.

最后,ISNULL()用于将NULLIF传递的NULL转换为空字符串。

ISNULL(NULL,'')          -- this would return ''
ISNULL(' Smith','')      -- this would return ' Smith'

#9

LTrim(RTrim(Replace(IsNull(Firstname + ' ', '') + isNull(MiddleName, '') + IsNull(' ' + LastName, ''), ' ', ' ')))

LTrim(RTrim(替换(IsNull(名字+'','')+ isNull(MiddleName,'')+ IsNull(''+ LastName,''),'',''))))

#10

Select firstname, middlename, lastname, ProvidedName = 

RTrim(Coalesce(FirstName + ' ','') 
+ Coalesce(MiddleName + ' ', '')
+ Coalesce(LastName + ' ', '')
+ COALESCE('' + ' ', '')
+ COALESCE(NULL, ''))

From names

#1