SQL Cannot find if blank field is NULL or ''-CodePudding

So I have a SSIS package that uses an expression to replace some text with '' and then this is loaded into the database.

If I do a select query in the table in SSMS such as;

SELECT * FROM xxx
WHERE Email IS NULL OR Email = ''

It returns no results although I can visually see there are records with a blank Email field.

I have also tried;

  SELECT * FROM xxx
  WHERE (LEN(ISNULL(Email,'')) = 0);

My SSIS expression;

LOWER(REPLACE([Email],"Â",""))

Are there any solutions?

EDIT - As stated below, I have casted the field as varbinary and they have a value of 0xA0.

EDIT 2 - The answer that worked for me was WHERE EMAIL LIKE '%' CHAR(160) '%'

CodePudding user response：

Can you try to use

WHERE EMail LIKE '%'   CHAR(160)   '%'

160 is the ascii for "non breaking space".

CodePudding user response：

Try :

SELECT * FROM xxx
WHERE Email IS NULL OR Email NOT LIKE REPLICATE('[[email protected]_]', LEN(Email))

CodePudding user response：

you can use the len function, if it is zero then email will contain an empty string, if it is null then email will be null.
This will find any rows where email is empty, null or has a non printable char

where len(email) = 0 or len(email) is null