Filter on a specific column in my table is not working

Question

I am using SQL Server 2014 and I have this simple T-SQL query:

USE MyDatabase

SELECT * FROM [t1]

WHERE DATALENGTH([Email]) > 0 OR [Email] <> ' '

I want to exclude all records where the Email field appears BLANK or NULL.

When running the above query, it is also extracting records with BLANK (appears empty) and NULLS.

What is the issue with my FILTER syntax here?

Answer 1

You can go for simple condition.

USE MyDatabase

SELECT * FROM [t1]

WHERE LEN(Email) > 0 

UPDATE: Simple test

SELECT * FROM (VALUES ('email'),(null),('')) as t(Val)
where len(val) > 0

Val
email

Answer 2

Seek records where trimming the data still results in some length:

SELECT * 
FROM [t1]
WHERE DATALENGTH(RTRIM([Email])) > 0

But actually, tidying the data up would be better than keep catering for junk in the field

UPDATE t1 SET Email = NULLIF(LTRIM(RTRIM(Email)),'')

This way all your emails end up trimmed of spaces or null - all "blanks" become null which means they automatically drop out of queries. It also means the column can be indexed for fast searching and no longer has to be manipulated on every query

Answer 3

Your query should be like this. Here some testing data given

CREATE TABLE T(
NAME VARCHAR(10),
EMAIL VARCHAR(30)
)

insert into T
SELECT 'A','[email protected]'
insert into T
SELECT 'B',''
insert into T
SELECT 'B',' '
insert into T
SELECT 'B','    '
insert into T
SELECT 'B',NULL

SELECT * FROM t
WHERE LEN(RTRIM(LTRIM(EMAIL)))>0