有關在 nvarchar 和數字欄位中處理 NULL 和空字元串的問題
我知道這裡經常會出現類似的問題。在發布這些之前我已經搜尋過,但我沒有找到任何可以完全回答我的問題的 QA 執行緒。在表格中,我基本上必須將
NULL
s、空字元串和(純)空格視為“空白”併計算非空白單元格的數量。該表包含numeric
,bit
和nvarchar
列的混合。Q1在表中
TABLE1
,我有一列COLUMN1 nvarchar(32)
具有以下數據分佈:Value RowCount N/A 80 -- string 'N/A' NULL 20 -- actual nulls
為什麼以下最後一個查詢返回意外結果?
SELECT SUM(CASE WHEN COLUMN1 IS NOT NULL THEN 1 ELSE 0 END) FROM TABLE1 -- returns 80, as expected SELECT SUM(CASE WHEN COLUMN1 NOT IN (NULL, '') THEN 1 ELSE 0 END) FROM TABLE1 -- returns 80, as expected SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END) FROM TABLE1 -- returns 80, but I expected 100.
Q2我有另一列
COLUMN2 numeric(18, 0)
填充了沒有NULL
s 或空字元串的值(但它可能包含一個/兩個)。但是由於我不明白的原因,下面的第二個查詢失敗了。SELECT SUM(CASE WHEN COLUMN2 NOT IN ('', NULL) THEN 1 ELSE 0 END) FROM TABLE1 -- returns full rowcount (100), as expected. SELECT SUM(CASE WHEN COLUMN2 NOT IN (NULL, '') THEN 1 ELSE 0 END) FROM TABLE1 --query FAILS! (Msg 8114, Level 16, State 5, Line 1. Error converting data type varchar to numeric.)
Q3無論列的數據類型如何,我要求檢查一列是否存在 NULL、空字元串和純空格,什麼是包羅萬象的表達式?如果我的列名來自 (cursorized) 變數
@column
,我應該將它包含在什麼中並將其與它進行比較?我嘗試使用cast
tonvarchar
和使用LTRIM
/RTRIM
,但坦率地說,我在這一點上有點迷茫。我正在使用 SQL Server 2008。感謝您閱讀本文並提供幫助。
第一季度
SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END) -- returns 80, but I expected 100.
為什麼你會期望 100 行?您有 20 行列所在的位置
NULL
。您的表達式計算為:SELECT SUM(CASE WHEN COLUMN1 <> '' THEN 1 ELSE 0 END)
由於
NULL
意味著未知,因此相等或不等比較將產生未知(在這種情況下是錯誤的,或者更準確地說,是不正確的)。當 column1 為 null 時,SQL Server 無法告訴您它是等於'foo'
還是不等於'foo'
.第二季度
該錯誤是由於隱式轉換和表達式的順序造成的。在第一個查詢中,您首先與字元串進行比較,然後與
NULL
. 變成了NULL
一個字元串,因為它後來被引用了,所以底層的列(你應該在執行計劃中看到)被隱式地轉換為一個字元串。在第二個查詢中,您正在與NULL
第一個進行比較,因此要確定表達式的數據類型,它必須檢查表。該表包含一個數字,因此第一個參數與 相同CONVERT(NUMERIC(18,2), NULL)
,然後它嘗試將空字元串轉換為數字。試試這個看看為什麼它不起作用:SELECT CONVERT(DECIMAL(10,2), '');
第三季度
為了對所有數據類型使用相同的表達式,您必須能夠將它們全部轉換為相同的數據類型。所以假設我有一張桌子:
CREATE TABLE #foo(a VARCHAR(30), b NUMERIC(18,2)); INSERT #foo SELECT '1', NULL; INSERT #foo SELECT NULL, 4.5; INSERT #foo SELECT '', 5.5;
現在比較這四個表達式的結果:
SELECT a FROM #foo WHERE COALESCE(NULLIF(RTRIM(a), ''), '') <> ''; SELECT a FROM #foo WHERE COALESCE(NULLIF(RTRIM(a), ''), '') = ''; SELECT b FROM #foo WHERE COALESCE(NULLIF(RTRIM(b), ''), '') <> ''; SELECT b FROM #foo WHERE COALESCE(NULLIF(RTRIM(b), ''), '') = '';