Sql-Server

有關在 nvarchar 和數字欄位中處理 NULL 和空字元串的問題

  • August 1, 2012

我知道這裡經常會出現類似的問題。在發布這些之前我已經搜尋過,但我沒有找到任何可以完全回答我的問題的 QA 執行緒。在表格中,我基本上必須將NULLs、空字元串和(純)空格視為“空白”併計算非空白單元格的數量。該表包含numeric,bitnvarchar列的混合。

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)填充了沒有NULLs 或空字元串的值(但它可能包含一個/兩個)。但是由於我不明白的原因,下面的第二個查詢失敗了。

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,我應該將它包含在什麼中並將其與它進行比較?我嘗試使用casttonvarchar和使用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), ''), '') = '';

引用自:https://dba.stackexchange.com/questions/21784