0

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?

user3115933
  • 4,303
  • 15
  • 54
  • 94
  • 1
    Your query won't return NULLs. Use of OR is your problem - datalength(email)>0 is true for an email of ' ', so they will return. Changing it to AND would help, but emails of two spaces would still return – Caius Jard Jul 19 '21 at 06:23

3 Answers3

0

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
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
  • `LEN(Email) > 0` will be enough [test](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=afc71846edac0d2085f098c5bacff63b) – id'7238 Jul 19 '21 at 07:56
0

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

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • 1
    LEN , by default ignores trailing blanks, when calculating length. No need for RTRIM. https://learn.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql?view=sql-server-ver15 – Venkataraman R Jul 19 '21 at 06:24
  • Is there a disadvantage to using [`trim`](https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-ver15) instead of `rtrim` _and_ `ltrim`? – HABO Aug 02 '21 at 20:05
  • No, it's just only been "a thing" since SQLS 2017 and some old habits die hard – Caius Jard Aug 02 '21 at 20:17
0

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','ab@gmail.com'
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
Ashraf Uddin
  • 82
  • 9
  • Is there a disadvantage to using [`trim`](https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-ver15) instead of `rtrim` _and_ `ltrim`? From [`len`](https://learn.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql?view=sql-server-ver15): "Returns the number of characters of the specified string expression, **excluding trailing spaces**." That means that `ltrim`, while a bit sneaky, would suffice. – HABO Aug 02 '21 at 20:03