How can we check in a SQL Server WHERE condition whether the column is not null and not the empty string ('')?
Add a comment
|
10 Answers
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.
9 Comments
Lieven Keersmaekers
Why not
WHERE COALESCE(column, '') <> ''?
Lamak
Because if
column has an index, then your query will probably not use it
Martin Smith
@Caltor - no they both get resolved to the same symbol during parsing and so are entirely indistinguishable dba.stackexchange.com/a/155670/3690
Martin Smith
@robotik - SQL Server ignores trailing spaces in most comparison operations, so
' ' is just treated the same as '' when it comes to = or <> stackoverflow.com/q/17876478/73226
robotik
Wow, never knew, thank you! All those years writing
LTRIM(RTRIM( for nothing :D |
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!).
Comments
in basic way
SELECT *
FROM [TableName]
WHERE column_name!='' AND column_name IS NOT NULL
1 Comment
SJHowe
No need to check that it is not NULL, the column_name !='' will do that. So expression can be simplified.
You can use either one of these to check null, whitespace and empty strings.
WHERE COLUMN <> ''
WHERE LEN(COLUMN) > 0
WHERE NULLIF(LTRIM(RTRIM(COLUMN)), '') IS NOT NULL
1 Comment
Vasiliy Zverev
LTRIM(RTRIM()) is not needed because MS SQL trims spaces automatically on comparison. So NULLIF(COLUMN, '') and NULLIF(LTRIM(RTRIM(COLUMN)), '') are the same.Coalesce will fold nulls into a default:
COALESCE (fieldName, '') <> ''
Comments
An index friendly way of doing this is:
where (field is not null and field <> '')
If there aren't many rows or this field isn't indexed, you can use:
where isnull(field,'') <> ''
Comments
Just check: where value > '' -- not null and not empty
-- COLUMN CONTAINS A VALUE (ie string not null and not empty) :
-- (note: "<>" gives a different result than ">")
select iif(null > '', 'true', 'false'); -- false (null)
select iif('' > '', 'true', 'false'); -- false (empty string)
select iif(' ' > '', 'true', 'false'); -- false (space)
select iif(' ' > '', 'true', 'false'); -- false (tab)
select iif('
' > '', 'true', 'false'); -- false (newline)
select iif('xxx' > '', 'true', 'false'); -- true
--
--
-- NOTE - test that tab and newline is processed as expected:
select 'x x' -- tab
select 'x
x' -- newline
Comments
Check the not null condition and empty string in SQL command is use 'is null / not null' and '!='. please try this sample pattern script:
SELECT * FROM [Employee]
WHERE
EMail is not null -- not null check
and Email != '' -- not empty check
Comments
For some kind of reason my NULL values where of data length 8. That is why none of the abovementioned seemed to work. If you encounter the same problem, use the following code:
--Check the length of your NULL values
SELECT DATALENGTH(COLUMN) as length_column
FROM your_table
--Filter the length of your NULL values (8 is used as example)
WHERE DATALENGTH(COLUMN) > 8
1 Comment
Martin Smith
This was presumably a string containing the word
NULL of NVARCHAR datatype, not an actual NULL. To find those strings you can just use = N'NULL' as they are just regular strings!check this way where Ph != '' and Ph = '123456780'
1 Comment
Rameesh Puthukkudy
WHERE LEN(Ph) > 0