You can use COALESCE in conjunction with NULLIF for a short, efficient solution: COALESCE( NULLIF(yourField,”) , ‘0’ ) The NULLIF function will return null if yourField is equal to the second value (” in the example), making the COALESCE function fully working on all cases: QUERY | RESULT ——————————————————————————— SELECT COALESCE(NULLIF(null ,”),’0′) | ‘0’ SELECT … Read more
=INDEX(B2:D2,MATCH(FALSE,ISBLANK(B2:D2),FALSE)) This is an Array Formula. After entering the formula, press CTRL + Shift + Enter to have Excel evaluate it as an Array Formula. This returns the first nonblank value of the given range of cells. For your example, the formula is entered in the column with the header “a” A B C D … Read more
This problem reported on Microsoft Connect reveals some differences between COALESCE and ISNULL: an early part of our processing rewrites COALESCE( expression1, expression2 ) as CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END. In [this example]: COALESCE ( ( SELECT Nullable FROM Demo WHERE SomeCol = 1 ), 1 ) we generate: … Read more
Do not create a user function is you want speed. Instead of this: coalescenonempty(col1,col2||’blah’,col3,’none’); do this: COALESCE(NULLIF(col1,”),NULLIF(col2||’blah’,”),NULLIF(col3,”),’none’); That is, for each non-constant parameter, surround the actual parameter with NULLIF( x ,”).
Apache Commons Lang 3 ObjectUtils.firstNonNull(T…) Java 8 Stream Stream.of(T…).filter(Objects::nonNull).findFirst().orElse(null)
C# already lets us substitute values for null with ??. So all we need is an extension that converts an empty string to null, and then we use it like this: s.SiteNumber.NullIfEmpty() ?? “No Number”; Extension class: public static class StringExtensions { public static string NullIfEmpty(this string s) { return string.IsNullOrEmpty(s) ? null : s; … Read more
With string type columns like character(2) (as you mentioned later), the displayed concatenation just works because, quoting the manual: […] the string concatenation operator (||) accepts non-string input, so long as at least one input is of a string type, as shown in Table 9.8. For other cases, insert an explicit coercion to text […] … Read more
COALESCE is more modern function that is a part of ANSI-92 standard. NVL is Oracle specific, it was introduced in 80‘s before there were any standards. In case of two values, they are synonyms. However, they are implemented differently. NVL always evaluates both arguments, while COALESCE usually stops evaluation whenever it finds the first non-NULL … Read more
The expression stringexpression = ” yields: TRUE .. for ” (or for any string consisting of only spaces with the data type char(n)) NULL .. for NULL FALSE .. for anything else So to check for: “stringexpression is either NULL or empty”: (stringexpression = ”) IS NOT FALSE Or the reverse approach (may … Read more