sql server - SQL Isnull is not working and returns a value even if it is a space

Question

I am using SQL Server 2016 and came across an issue with the isnull function. The value in that field is a space but keeps getting selected, below is my code:

SELECT * FROM table
WHERE  isnull (field1,'')<>''
       AND field1<>' '

If I copy and paste from field1, it is one space ' '.

Thanks

Answer 1

Your space is not normal space (hex 0x20, decimal 32)

select ASCII(LEFT(field1, 1), * From table
where  isnull (field1,'')<>''

Most likely it is one of

• Linefeed decimal 10
• Carriage return decimal 13
• Null decimal 0
• hard space decimal 160
• tab decimal 9 (as per comments on question from @SeanLange)