This will select all rows where some_col is NULL or '' (empty string)
SELECT * FROM table WHERE some_col IS NULL OR some_col = '';
This will select all rows where some_col is NULL or '' (empty string)
SELECT * FROM table WHERE some_col IS NULL OR some_col = '';
As defined by the SQL-92 Standard, when comparing two strings of differing widths, the narrower value is right-padded with spaces to make it is same width as the wider value. Therefore, all string values that consist entirely of spaces (including zero spaces) will be deemed to be equal e.g.
'' = ' ' IS TRUE
'' = ' ' IS TRUE
' ' = ' ' IS TRUE
' ' = ' ' IS TRUE
etc
Therefore, this should work regardless of how many spaces make up the some_col value:
SELECT *
FROM T
WHERE some_col IS NULL
OR some_col = ' ';
or more succinctly:
SELECT *
FROM T
WHERE NULLIF(some_col, ' ') IS NULL;
Videos
An extension to @db2's answer with less (read:zero) hand-wrangling:
DECLARE @tb nvarchar(512) = N'dbo.[table]';
DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
+ N' WHERE 1 = 0';
SELECT @sql += N' OR ' + QUOTENAME(name) + N' IS NULL'
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@tb)
AND is_nullable = 1;
EXEC sys.sp_executesql @sql;
You should list out all the columns as per JNK's comment.
WHERE c1 IS NULL OR c2 IS NULL OR c3 IS NULL
A somewhat less efficient approach that avoids this is below though.
;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' AS ns)
SELECT *
FROM YourTable AS T1
WHERE (
SELECT T1.*
FOR XML PATH('row'), ELEMENTS XSINIL, TYPE
).exist('//*/@ns:nil') = 1
(Based on this SO answer)
If you only want to match "" as an empty string
WHERE DATALENGTH(COLUMN) > 0
If you want to count any string consisting entirely of spaces as empty
WHERE COLUMN <> ''
Both of these will not return NULL values when used in a WHERE clause. As NULL will evaluate as UNKNOWN for these rather than TRUE.
CREATE TABLE T
(
C VARCHAR(10)
);
INSERT INTO T
VALUES ('A'),
(''),
(' '),
(NULL);
SELECT *
FROM T
WHERE C <> ''
Returns just the single row A. I.e. The rows with NULL or an empty string or a string consisting entirely of spaces are all excluded by this query.
SQL Fiddle
WHERE NULLIF(your_column, '') IS NOT NULL
Nowadays (4.5 years on), to make it easier for a human to read, I would just use
WHERE your_column <> ''
While there is a temptation to make the null check explicit...
WHERE your_column <> ''
AND your_column IS NOT NULL
...as @Martin Smith demonstrates in the accepted answer, it doesn't really add anything (and I personally shun SQL nulls entirely nowadays, so it wouldn't apply to me anyway!).
I'd rather not write a bunch of AND clauses, so is there a quick, efficient way to do this?
I'm importing some data with 10 fields into a SQL Server from a CSV file. Occasionally this file has null/empty values across all the cells/columns.
What I'd like to do is just write one relatively short sql statement to simply count (at first) all these rows. I'd rather do it without doing something like:
...and (column1 is null or column1 = '')
...and (column2 is null or column2 = '')
etc...
Is there a good way to do this, or am I stuck with the above?
Does this do what you want?
SELECT *
FROM UserProfile
WHERE PropertydefinitionID in (40, 53)
AND ( PropertyValue is NULL
or PropertyValue = '' );
Here's a slightly different way:
SELECT *
FROM UserProfile
WHERE PropertydefinitionID in (40, 53)
AND (LEN(ISNULL(PropertyValue,'')) = 0)
I think this:
SELECT
ISNULL(NULLIF(listing.Offer_Text, ''), company.Offer_Text) AS Offer_Text
FROM ...
is the most elegant solution.
And to break it down a bit in pseudo code:
// a) NULLIF:
if (listing.Offer_Text == '')
temp := null;
else
temp := listing.Offer_Text; // may now be null or non-null, but not ''
// b) ISNULL:
if (temp is null)
result := true;
else
result := false;
SELECT
CASE WHEN LEN(listing.OfferText) > 0 THEN listing.OfferText
ELSE COALESCE(Company.OfferText, '') END
AS Offer_Text,
...
In this example, if listing.OfferText is NULL, the LEN() function should also return NULL, but that's still not > 0.
Update
I've learned some things in the 5 1/2 years since posting this, and do it much differently now:
COALESCE(NULLIF(listing.OfferText,''), Company.OfferText, '')
This is similar to the accepted answer, but it also has a fallback in case Company.OfferText is also null. None of the other current answers using NULLIF() also do this.
Select *
From Table
Where (col is null or col = '')
Or
Select *
From Table
Where IsNull(col, '') = ''
If you need it in SELECT section can use like this.
SELECT ct.ID,
ISNULL(NULLIF(ct.LaunchDate, ''), null) [LaunchDate]
FROM [dbo].[CustomerTable] ct
You can replace the null with your substitution value.