I'm trying to update field by lopping off its last character but I'm getting an error. Apparently, if the field is NULL when it tries to evaluate LEN it throws the error.
Here's what I have:
UPDATE table SET table.field = left(field, Len(field)-1 WHERE field is not null.
Can someone help with my syntax?
Are you getting a syntax error, or a runtime error? If it is syntax error, that is because you have a missing right bracket.
UPDATE table SET table.field = left(field, Len(field)-1) WHERE field is not null
For this statement, if you are getting a runtime error, what is the error you are getting? You could get runtime error, for example, if you had some rows in which field had an empty string. But field being null should not generate a runtime error.
UPDATE table
SET field = left(field, Len(field)-1)
WHERE Len(field) > 0
The message says: Msg 537, Level 16, State 2, Line 132
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
This statement worked. Thank you. I'm curious, though, why the statement testing for NULL didn't work.
SQL allows an empty string, '', and that is not a "NULL", it's just empty. But its length will be 0.
Note that once we have the WHERE on the Len(), we don't need to check for NULL any more, since NULL values will never have "Len() > 0". Any function on a NULL column will always return NULL, which can never be =, ge, or le to any value.
Fyi, here's a sample of the original problem:
SELECT LEFT(field, Len(field) - 1) FROM ( VALUES('') ) AS test(field)
Thank you again. That's helpful.
Are there any situations where my syntax would work (i.e., "...WHERE field IS NOT NULL)? Under what circumstances would a field be NULL and not empty? If a field is NULL is it also empty?
A column that is NULL is never considered empty. NULL is "unknown". That's the best way to think about it.
So, what is the length of an unknown value? The only possible answer is unknown (NULL).
Is an unknown value > 7? Again, that too can only be unknown (NULL).
Remember, a WHERE clause must be true to include a row. Not "not false" even, but affirmatively, 100% true. That's why if you have a comparison in the WHERE you technically don't also have to test for NULL, because NULL will never pass the comparison anyway.
WHERE column1 = 5
Would also exclude NULL column1 values, since those are not proven equal to 5.
The biggest impact is something like this:
WHERE ISNULL(column1, '') <> ''
The function on the column will prevent an index seek from being used, and you don't need it here.
WHERE column1 <> ''
will automatically leave out NULLs, for the same reason as before.
Great information. Thanks.