Single Space String means Empty String for Dynamic Input SQL: Update WHERE CLAUSE...???

Question

I feel like I am going crazy here. I am using a dynamic input tool to update combinations of fields for a WHERE clauses for an Oracle DB from an external excel document. For some reason it was not recognizing empty cells and passing those through to the SQL. Only giving me back data for ones that were not empty even though the field in the database can be empty. I found some obscure community thread where someone somehow was able to test for blanks with a single space between quotes and it worked for them so I tried it. It worked. What is going on?! Everything I know clearly distinguishes the difference between the two... a blank string is '' and not ' '. I have ran into many errors in the past cleaning data with leading and trailing space. Has anyone seen anything like this before?

KGT · Accepted Answer

Disclaimer straight up that I haven't been in the world of backend DB's for years and so info may be old.

I seem to remember a difference with Oracle in the way it recognises an Empty Cell. In Oracle SQL there was not a noticeable difference between NULL and Empty, hence it was common(?) to put a space in if the cell was meant to be empty instead of Null. Often this was due to the column having a "Not Null" condition.

Hope that gives more info to track down the explanation.