仍在学习SQL,如何防止FirstName
和LastName
之间的结果集中出现多余空间?
USE AdventureWorks;
GO
SELECT FirstName + ' ' + COALESCE (MiddleName, '') + ' ' + LastName
FROM Person.Contact;
您可以
FirstName + ' ' + COALESCE (MiddleName + ' ', '') + LastName
如果MiddleName
是NULL
,那么MiddleName + anything
也是
try:
USE AdventureWorks;
GO
SELECT FirstName + COALESCE (' ' +MiddleName, '') + ' ' + LastName
FROM Person.Contact;
如果MiddleName
为NULL,则' '+MiddleName
变为NULL,并且COALESCE返回空字符串,从而防止了额外的空间。
一个空的中间名加上一个空格将导致空,所以:
FirstName + ' ' + COALESCE(MiddleName + ' ', '') + LastName
应该这样做。
就我个人而言,我会在COALESCE()
函数中添加空格。考虑额外处理空字符串:
SELECT FirstName + ' '
+ COALESCE(NULLIF(MiddleName, '') + ' ', '')
+ LastName
FROM Person.Contact;