如何删除具有空值的列?

How can I remove NULL cells in my query because some records appears even if the CompanyName doesnt exist?

如何删除查询中的空单元格，因为即使连名不存在也会出现一些记录?

SELECT ClientID, CompanyName, FirstName, LastName, Street, City
  FROM Client
  WHERE CompanyName LIKE '%G%' Or FirstName LIKE '%J%' OR LastName LIKE '%J%'
  OR Street LIKE '%J%' OR City LIKE '%J%' Or ContactNo LIKE '%0%'
  AND ClientTypeID = 2

5 个解决方案

#1

To exclude all rows where CompanyName is null, you need to add

要排除CompanyName为空的所有行，需要添加

AND CompanyName is NOT NULL

you might need parentheses after your WHERE and after your last OR condition

您可能需要在您的WHERE后面和上一个条件之后加上括号

#2

Naively, just use

天真,只使用

SELECT ClientID, CompanyName, FirstName, LastName, Street, City
FROM Client
WHERE CompanyName is not NULL and (CompanyName LIKE '%G%' Or FirstName LIKE '%J%' Or LastName LIKE '%J%' Or Street LIKE '%J%' Or City LIKE '%J%' Or ContactNo LIKE '%0%'
AND ClientTypeID = 2)

#3

SELECT ClientID, CompanyName, FirstName, LastName, Street, City
FROM Client
WHERE (CompanyName LIKE '%G%' Or FirstName LIKE '%J%' Or LastName LIKE '%J%' Or Street LIKE '%J%' Or City LIKE '%J%' Or ContactNo LIKE '%0%' AND ClientTypeID = 2) AND CompanyName IS NOT NULL;

The problem is that you have or statements. Even if CompanyName doesn't fit row LastName might fit. Since you use or's in your filter result will be then included.

问题是你有或声明。即使公司名称不适合行LastName也可以。由于您使用或在您的筛选结果将包括。

And you are mixing or's, and's both without any parenthesis. You shouldn't do that. You should eighter write:

你混合了或者，没有任何括号。你不应该这样做。你应该八写:

SELECT ClientID, CompanyName, FirstName, LastName, Street, City
FROM Client
WHERE (CompanyName LIKE '%G%' Or FirstName LIKE '%J%' Or LastName LIKE '%J%' Or Street LIKE '%J%' Or City LIKE '%J%' Or ContactNo LIKE '%0%') AND ClientTypeID = 2;

或

SELECT ClientID, CompanyName, FirstName, LastName, Street, City
FROM Client
WHERE CompanyName LIKE '%G%' Or FirstName LIKE '%J%' Or LastName LIKE '%J%' Or Street LIKE '%J%' Or City LIKE '%J%' Or (ContactNo LIKE '%0%' AND ClientTypeID = 2);

#4

CompanyName like '%G'

公司名称(如“% G”

will fail if CompanyName is null, won't it?

如果连名为空，将会失败，不是吗?

If your DB doesn't behave that way, just add

如果你的DB不是那样的，那就添加

AND CompanyName IS NOT NULL

连名不是空的

#5

remove all nulls from all columns

从所有列中删除所有null

declare @ex as nvarchar(max) = ''
declare @_null as nvarchar(max)
set @_null = ''

select @_null += 'isnull(' + QUOTENAME(column_name) + ',0) as ' + QUOTENAME(column_name) + ','

from sample.INFORMATION_SCHEMA.columns where TABLE_NAME = 'table';

set @_null = left(@_null,len(@_null) - 1);

set @ex = 'select ' + @_null + ' from table';

exec sp_executesql @ex;

#1