-1

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) + '%'

PatBentley921
  • 45
  • 4
  • Your question is very similar to [this](https://stackoverflow.com/a/15663302/17912604) – almezj Feb 24 '22 at 13:42
  • 1
    Is it possible your `Email` columns contain whitespace? Try `WHERE (Email IS NULL OR LTRIM(RTRIM(Email)) = '')` – O. Jones Feb 24 '22 at 13:42
  • 1
    or non-display characters like tab... another technique is to do a length() on the rows that appear blank and if the length is > 1 you know you have a non-display character. – xQbert Feb 24 '22 at 13:44
  • 5
    Visually locate a row that "is blank" and use the PK of the value to select the email column and cast that column to varbinary of the appropriate length to actually SEE what the column contains. Presumably it contains non-printable characters and you will need to understand what they are in order to cleanse these values. – SMor Feb 24 '22 at 13:44
  • @O.Jones it wouldn't matter if it was multiple space characters, SQL Server ignores trailing spaces for equality. `''` = `' '` = `' '`. – Thom A Feb 24 '22 at 13:50
  • @SMor, I have casted as varbinary and I am getting this result for all fields - '0xA0'. How would I go about cleansing the values as I cant just input WHERE Email = '0xA0' as it still return 0 records nor can I do WHERE CAST([Email] as varbinary) = '0xA0' – PatBentley921 Feb 24 '22 at 13:51
  • @PatBentley921 can you try to use WHERE EMail LIKE '%' + CHAR(160) + '%' ,, 160 is the ascii for "non breaking space". – Eren Temelli Feb 24 '22 at 13:54
  • @ErenTemelli this has worked for me thank you! I don't know how to say this is the answer so I think you need to create an answer post. – PatBentley921 Feb 24 '22 at 13:57
  • @Charlieface Neither of those clauses worked for me – PatBentley921 Feb 24 '22 at 13:58
  • `0xA0` is a non-breaking space, which is not a space and therefore not ignored. You can do `WHERE Email = CHAR(160)` or `NCHAR(160)` if unicode – Charlieface Feb 24 '22 at 13:58
  • @PatBentley921 posted as answer. glad to hear that. – Eren Temelli Feb 24 '22 at 13:59
  • @xQbert when the value is '' then len(column) will be zero. So it is not > 1 but still it is not empty – GuidoG Feb 24 '22 at 15:56
  • @guidoG you're missing the point. non-display characters still take up length. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bcbba29f2bb0fabf95d66793cb1282ae may look like empty set '' but it's not! Tough to tell char(13) or char(9) from '' but length will... but '' is 0 and '' is named "EMPTY SET" while would be length NULL. The issues is '' or NULL were still returning what appeared to be blank rows. But they weren't blank as length would show. – xQbert Feb 24 '22 at 16:25
  • @xQbert I think you have misunderstood me. I am not saying that non-display characters take no space. I am just saying that comparing the length only to >1 is not enough, when the column has an empty string the len will be zero, and an empty string is also considered empty I guess – GuidoG Feb 25 '22 at 06:33
  • @GuidoG but the attempted SQL using `WHERE Email IS NULL OR Email = ''` would have caught such records. so we can ignore them already right? (I'm probably still missing something) Several solid responses have been provided so this is moot at this point I think. – xQbert Feb 25 '22 at 14:00

3 Answers3

2

Can you try to use

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

160 is the ascii for "non breaking space".

Eren Temelli
  • 333
  • 1
  • 14
0

Try :

SELECT * FROM xxx
WHERE Email IS NULL OR Email NOT LIKE REPLICATE('[A-Za-z0-9@.-_]', LEN(Email))
SQLpro
  • 3,994
  • 1
  • 6
  • 14
0

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
GuidoG
  • 11,359
  • 6
  • 44
  • 79