Início / dba / Perguntas / 21784
Accepted
Anonymous Maximus
Asked: 2012-08-01 15:47:33 +0800 CST

Perguntas sobre como lidar com NULLs e strings vazias em nvarchar e campos numéricos

  • 772

Eu entendo que perguntas semelhantes a essas aparecem frequentemente por aqui. Pesquisei antes de postar isso, mas não encontrei nenhum tópico de controle de qualidade que responda completamente às minhas perguntas. Em uma tabela, basicamente tenho que tratar NULLs, strings vazias e espaços em branco (puros) como 'vazios' e contar o número de células não em branco. A tabela contém uma combinação de numerice bitcolunas nvarchar.

Q1 Na tabela TABLE1, tenho uma coluna COLUMN1 nvarchar(32)com a seguinte distribuição de dados:

Value    RowCount
N/A      80             -- string 'N/A'
NULL     20             -- actual nulls

Por que a última das consultas a seguir retorna resultados inesperados?

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 Eu tenho outra coluna COLUMN2 numeric(18, 0)preenchida com valores sem NULLs ou strings vazias (mas pode conter um/ambos). Mas a segunda das consultas abaixo falha devido a um motivo que não entendo.

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 O que é uma expressão abrangente para meu requisito de verificar uma coluna para NULLs, strings vazias e espaço em branco puro, independentemente do tipo de dados de uma coluna? Se meu nome de coluna vier de uma variável (cursorizada) @column, o que devo colocá-lo e compará-lo? Eu tentei trabalhar com castto nvarchare usar LTRIM/ RTRIM, mas francamente estou um pouco perdido neste momento.

Estou usando o SQL Server 2008. Obrigado por ler isso e por sua ajuda.

sql-server sql-server-2008

3 respostas

  1. Best Answer

    Q1

    SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END)
-- returns 80, but I expected 100.

    Por que você esperaria 100 linhas? Você tem 20 linhas onde a coluna é NULL. Sua expressão é avaliada como:

    SELECT SUM(CASE WHEN COLUMN1 <> '' THEN 1 ELSE 0 END)

    Como NULLmeios desconhecidos, uma comparação de igualdade ou desigualdade resultará em desconhecido (e, neste caso, falso ou, mais pedantemente preciso, não verdadeiro). Quando column1 é nulo, o SQL Server não pode dizer se é igual 'foo'ou diferente de 'foo'.

    Q2

    O erro é devido à conversão implícita e à ordem das expressões. Na primeira consulta, você está comparando primeiro com uma string e depois com NULL. O NULLtorna-se uma string, porque foi referenciado posteriormente e, portanto, a coluna subjacente (como você deve ver no plano de execução) foi convertida implicitamente em uma string. Na segunda consulta, você está comparando com a NULLprimeira, portanto, para determinar o tipo de dados da expressão, deve-se verificar a tabela. A tabela contém um numérico, portanto, o primeiro argumento é o mesmo que CONVERT(NUMERIC(18,2), NULL)e, em seguida, tenta converter a string vazia em numérica. Tente isso para ver por que não funciona:

    SELECT CONVERT(DECIMAL(10,2), '');

    Q3

    Para usar a mesma expressão em todos os tipos de dados, você deve poder convertê-los todos para o mesmo tipo de dados. Digamos que eu tenha uma tabela:

    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;

    Agora compare os resultados dessas quatro expressões:

    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), ''), '') = '';
    • 4

  2. SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END) FROM TABLE1 -- ​​retorna 80, mas eu esperava 100.

    Como o valor NULL não é o mesmo que uma string vazia no SQL, o resultado obtido está correto e faz sentido em termos de SQL.

    Pense em NULL como "Valor Não Definido" e, como tal, não é o mesmo que uma string vazia (ou qualquer valor não nulo para esse mater) que é um valor definido. Do ponto de vista do inglês, null é o mesmo que nada, mas isso não é o mesmo no SQL.

    Agora, o que você espera que isso retorne?

    SELECT * FROM TABLE1 WHERE C1 NOT IN ('N/A')

    Dados seus dados acima, ele não retorna nenhuma linha.

    SELECT SUM(CASE WHEN COLUMN2 NOT IN (NULL, '') THEN 1 ELSE 0 END) FROM TABLE1 --query FAILS!

    Aqui a consulta falha porque você está tentando comparar um valor numérico com uma string vazia. Os tipos de dados não são os mesmos. Você precisa comparar valores de tipos de dados compatíveis para garantir o resultado correto.

    Eu não entendo completamente o que você quer dizer com Q3, então não poderei ajudar com isso.

    Tenha muito cuidado com Nulls eles são complicados.

    • 1

  3. As respostas já postadas estão corretas, mas aqui está o "take-away" prático: Para meus propósitos, "Uma entrada nula é a mesma coisa que uma entrada em branco". Se isso também for verdade no seu mundo, faça o seguinte: Para suas declarações de avaliação, adquira o hábito de usar isnull([fieldname],'') para varchars e isnull([fieldname],0) para campos numéricos. Dessa forma, seus campos nulos são sempre avaliados de forma consistente e você QUASE nunca terá resultados inesperados.

    • -2

relate perguntas