T-SQL LTRIM RTRIM對NULL的處理

LTRIM和RTRIM分別可以去掉資料左邊和右邊的空白字元，
如下述的範例，因為已知firstName和lastName可能右邊有空白字元，
所以使用RTRIM把空白字元去掉。

DECLARE @firstName NVARCHAR(50), @lastName NVARCHAR(50)
SET @firstName = 'Grady'
SET @lastName = 'Li         '

--沒有使用RTRIM()
SELECT 'My name is ' + @firstName + ' ' + @lastName + '. Nice to meet you.'
--結果如下
--My name is Grady Li         . Nice to meet you.

--使用RTRIM()
SELECT 'My name is ' + RTRIM(@firstName) + ' ' + RTRIM(@lastName) + '. Nice to meet you.'
--結果如下
--My name is Grady Li. Nice to meet you.

可是如果資料的內容有可能是NULL時，LTRIM和RTRIM執行的結果，
就不是我們所預期的。

例如使用者誤把所有資料填入firstName，而lastName則是資料庫預設的NULL。

DECLARE @firstName NVARCHAR(50), @lastName NVARCHAR(50)
SET @firstName = 'Grady Li     '
SET @lastName = NULL

SELECT 'My name is ' + RTRIM(@firstName) + ' ' + RTRIM(@lastName) + '. Nice to meet you.'
--結果如下
--NULL

其實往深一層探討，會出現這樣的情況，
是在於字串相加的規則中，對於NULL的處理所產生的問題。
字串使用「+」串聯時，如果其中有一個值是NULL，則結果為NULL。[1]

要解決這個問題的方法有兩種。
方法一，
針對當下的SESSION改變NULL處理的預設值，如下範例。

SET CONCAT_NULL_YIELDS_NULL OFF;
GO

DECLARE @firstName NVARCHAR(50), @lastName NVARCHAR(50)
SET @firstName = 'Grady Li     '
SET @lastName = NULL

SELECT 'My name is ' + RTRIM(@firstName) + ' ' + RTRIM(@lastName) + '. Nice to meet you.'
--結果如下
--My name is Grady Li . Nice to meet you.

這是利用CONCAT_NULL_YIELDS_NULL讓NULL結果不會輸出。

但是官方文件已經備註，此功能未來將鎖定為ON，使用者無法變更。
也就是不建議使用此解決方式。[2]

方法二，
使用COALESCE將NULL取代為空字串(不是空白字串)。
其實COALESCE並不是取代NULL;
而是它會回傳參數中，第一個非NULL的參數。[3]

範例如下，

DECLARE @firstName NVARCHAR(50), @lastName NVARCHAR(50)
SET @firstName = 'Grady Li     '
SET @lastName = NULL

SELECT 'My name is ' + COALESCE(RTRIM(@firstName), '')
	+ ' ' + COALESCE(RTRIM(@lastName), '') + '. Nice to meet you.'
--結果如下
--My name is Grady Li . Nice to meet you.

也就是COALESCE會去判斷我們傳進去的參數，
如果第一個參數不是NULL，就回傳。
如果第一個參數是NULL，就判斷第二個參數，以此類推。

以上，做為記錄。

參考文章
1. http://msdn.microsoft.com/en-us/library/ms177561.aspx
2. http://msdn.microsoft.com/en-us/library/ms176056(v=SQL.105).aspx
3. http://msdn.microsoft.com/en-us/library/ms190349.aspx