Can I format NULL values in string.Format?

Question

I was wondering if there's a syntax for formatting NULL values in string.Format, such as what Excel uses

For example, using Excel I could specify a format value of {0:#,000.00;-#,000.00,NULL}, which means display the numeric value as number format if positive, number format in parenthesis if negative, or NULL if the value is null

string.Format("${0:#,000.00;(#,000.00);NULL}", someNumericValue);

Edit

I'm looking for formatting NULL/Nothing values for all data types, not just numeric ones.

My example is actually incorrect because I mistakenly thought Excel used the 3rd parameter if the value was NULL, but it's actually used when the value is 0. I'm leaving it in there because it's the closest thing I can think of to what I was hoping to do.

I am hoping to avoid the null coalescing operator because I am writing log records, and the data is not usually a string

It would be much easier to write something like

Log(string.Format("Value1 changes from {0:NULL} to {1:NULL}", 
    new object[] { oldObject.SomeValue, newObject.SomeValue }));

than to write

var old = (oldObject.SomeValue == null ? "null" : oldObject.SomeValue.ToString());
var new = (newObject.SomeValue == null ? "null" : newObject.SomeValue.ToString());

Log(string.Format("Value1 changes from {0} to {1}", 
    new object[] { old, new }));

Linkwork

null (Nothing in Visual Basic) or 0 (zero)? – Adigun

@Adigun I'm looking for formatting null / Nothing – Linkwork

@JimMischel Sorry, I was thinking Excel formatted NULL values with the 3rd parameter. It's actually zeros. I'll update my question, but I'm leaving the Excel example in there since it's the closest thing I can think of to what I'm looking for. – Linkwork

Answer 1

You can define a custom formatter that returns "NULL" if the value is null and otherwise the default formatted string, e.g.:

foreach (var value in new[] { 123456.78m, -123456.78m, 0m, (decimal?)null })
{
    string result = string.Format(
        new NullFormat(), "${0:#,000.00;(#,000.00);ZERO}", value);
    Console.WriteLine(result);
}

Output:

$123.456,78
$(123.456,78)
$ZERO
$NULL

Custom Formatter:

public class NullFormat : IFormatProvider, ICustomFormatter
{
    public object GetFormat(Type service)
    {
        if (service == typeof(ICustomFormatter))
        {
            return this;
        }
        else
        {
            return null;
        }
    }

    public string Format(string format, object arg, IFormatProvider provider)
    {
        if (arg == null)
        {
            return "NULL";
        }
        IFormattable formattable = arg as IFormattable;
        if (formattable != null)
        {
            return formattable.ToString(format, provider);
        }
        return arg.ToString();
    }
}

Adigun

Thank you, that works exactly how I was hoping it would work! You've just made my life a whole lot easier :) – Linkwork

This is not working for me. I am writting a JsonValueFormatter and the null value is never entering the Format method. If I do string.Format(formatter, "Test: {0}, {1}, {2}", true, 10, null, "Hello") it returns Test: true, 10, , "Hello". – Bistro

Answer 2

I don't think there's anything in String.Format that will let you specify a particular format for null strings. A workaround is to use the null-coalescing operator, like this:

const string DefaultValue = "(null)";

string s = null;
string formatted = String.Format("{0}", s ?? DefaultValue);

Missis

See my updated question. I was hoping to avoid using ?? because the data is not usually a string – Linkwork

@Rachel: See my updated response. There's no reason you need to restrict the null-coalescing operator to strings. – Missis

Yes, but I want the default value to be "NULL" even if the data type is a number or a date. I cannot use someNumber ?? "NULL" because the string "NULL" is not the same data type as someNumber – Linkwork

Answer 3

Is this what you want?

string test;

test ?? "NULL"

Sindee

See my updated question. I was hoping to avoid using ?? because the data is not usually a string – Linkwork

Answer 4

It looks like String.Format for .NET acts the same way as Excel, i.e., you can use ; separator for positive, negative, and 0 values, but not NULL: http://msdn.microsoft.com/en-us/library/0c899ak8.aspx#SectionSeparator.

You will probably just have to handle the null value manually:

if (myval == null)
    // handle
else
    return String.Format(...);

Amari

Answer 5

You could use an extension method:

 public static string ToDataString(this string prm)
   {
       if (prm == null)
       {
           return "NULL";
       }
       else
       {
           return "'" + prm.Replace("'", "''") + "'";
       }
   }

Then in your code you can do:

string Field1="Val";
string Field2=null;

string s = string.Format("Set Value:{0}, NullValue={1}",Field1.ToDataString(), Field2.ToDataString());

Wynnie