String Comparison Error when Searching Blank Fields
Searching through a column in a spreadsheet I notice that the second line will exception ("Nullable object must have a value.") when it comes across a blank field, however the first line will succeed. I have to add the
Bool cast to the second line because otherwise I get "Cannot convert Nullable<bool> to bool". I assume this is the issue, but is there a way around that to allow blank fields to be checked?
keyFoundCell = _ws.Cells["a:a"].FirstOrDefault(cell => cell.Value?.ToString() == field.Key); keyFoundCell = _ws.Cells["a:a"].FirstOrDefault(cell => (bool)cell.Value?.ToString().Equals(field.Key, StringComparison.OrdinalIgnoreCase));
In both EPPlus and Excel Interop you can read a cell's contents using the
Text property instead of the
Value property if you want to operate on the cell's visible contents and avoid nulls.
Value returns an
object which might be null, but
Text returns the visible text as a
string which can be empty but won't be null.
If we're using
Value?.ToString() then chances are we'd be better off with
Text because that's a giveaway that we want the text we see, not the value.
Solved: How to exclude events with null fields in a search, In particular, I'm trying to exclude events that have a blank System Name field. I was trying to do it thusly: field!="null". In the search command, the text following an equal sign is considered a string. An Unexpected Error has occurred. Top. Fields can hold a null value for the following reasons: A parsing operation failed to parse a value. There is a mismatch from a lookup operator query. There is a missing field from a geo lookup operator query.
What's happening here is that the
?. operator will return
null right away if the left hand side of the operator is
null, the first line works because you're doing a direct comparison using the
== operator, which will return a
bool. In other words,
null == field.Key returns
null, of course).
The second line does not work without a cast because if the value is
null, then the
?. operator returns
null and the rest of the line is ignored (
.ToString() is never called). So the exception you're getting is due to the fact that the
if condition must return a
bool, yet it's returning a
One way to fix this is to simply check for
null first. This will not compare any objects where
cell.Value == null:
keyFoundCell = _ws.Cells["a:a"].FirstOrDefault(cell => cell.Value != null && cell.Value.ToString().Equals(field.Key, StringComparison.OrdinalIgnoreCase));
Another way you can do this is to use the static
Equals method of the
string class, which will allow one or more
null arguments. This will include results where
cell.Value == null (and will return true for those cases where
field.Key is also
keyFoundCell = _ws.Cells["a:a"].FirstOrDefault(cell => string.Equals(cell.Value?.ToString(), field.Key, StringComparison.OrdinalIgnoreCase));
Comparing 'null' values in Power Query, Recently I needed to do the very simple thing in Power Query. I have the Data contains nulls and comparison return an error. And this simple Correctly Sum Two Or More Columns in Power Query and Power BI cell in Excel could look like it is empty but can contain an empty string (i.e, “”). For the� All I'm trying to do is basically display "Input number" if a field is left blank and the form is submitted. I realize this probably isn't super difficult, but try as I might, I haven't been able to figure it out.
keyFoundCell = _ws.Cells["a:a"].FirstOrDefault(cell => (bool?)cell.Value?.ToString().Equals(field.Key, StringComparison.OrdinalIgnoreCase)) ?? false;
SQL: empty string vs NULL value, not string comparison which will tend to be slower (even for empty strings I suppose In your example if it is value directly from web field - I would use empty string. Which SQL query is more obvious and readable if you wanted to find users with an question, the issue is not null vs empty-string, it is a validation failure. Blank Cells. To evaluate the cells as Blank, you need to use either logical expression Equal to Blank (=””) of ISBLANK function inthe logical_test argument of the IF formula. In both methods logical_test argument returns TRUE if a cell is Blank, otherwise, it returns FALSE if the cell is Not Blank
How to compare two cells in Excel (string comparison), This tutorial will teach you how to automate the tedious and error-prone task of cell comparison and what formulas are best to use in each� This is because Oracle internally changes empty string to NULL values. Oracle simply won't let insert an empty string. On the other hand, SQL Server would let you do what you are trying to achieve. There are 2 workarounds here:
How NULL and empty strings are treated in PostgreSQL vs Oracle , Oracle reads empty strings as NULLs, while PostgreSQL treats them as considered to be a NULL in the query results, but an empty string. All you can do is to call equals() method on empty String literal and pass the object you are testing as shown below : String nullString = null; String empty = new String(); boolean test = "".equals(empty); // true System.out.println(test); boolean check = "".equals(nullString); // false System.out.println(check);
Use the ISBLANK function to test if a cell is empty or not. For example, =ISBLANK(A1) will return TRUE if A1 is empty, and FALSE if A1 contains text a formula (even if the formula returns an empty string ""). It's probably best to think of ISBLANK to mean "is empty" since it can return FALSE when cells look blank but aren't.
- Are you using Interop? Use
.Value. Or do you mean this:
- @ScottHannen Ah .Text seems to work. I'm using Epplus library which is similar to Interop. Want to drop that in as an answer?
- @RufusL cell doesn't seem to have a