polkadot (10/22/2013)
If a sql server table's column values1. allows nulls
2. originally came from another database
3. occasionally contains the word NULL in it
4. is empty half the time
what are all the conditions that you must evaluate for in the case of a CASE statement in which you handle for that field being 'empty'.
I am currently using the following statement:
(1) CASE WHEN LEN(realname) = 0 and LEN(computer_name) = 0 then email
but I've also used:
(2) CASE WHEN realname IS NULL OR realname = '' and computer_name IS NULL OR computer_name = '' THEN email
WHICH IS BETTER, 1 OR 2?
According to your rules neither of these is accurate. They will both allow columns with the string literal 'NULL'. If you are ok with ignoring rule #3 than I would do a slightly different version of (2).
Case when ISNULL(realname, '') = '' AND ISNULL(computer_name, '') = '' then email
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/