SSRS function with regex

Question

I am creating a report using SSRS for a phone number field in my database as a string value. I need to format a string value in phone number format (555) 555-1212. If the value is null, display nothing.

For example, a table in my database has a phone number column and some values are NULL.

I got a regex that formats the phone number fine.

=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.](\d{3})[ -.](\d{4})","($1) $2-$3")

However if I do this:

=IIf(Fields!Phone.Value is nothing, "", System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value,"(\d{3})[ -.](\d{3})[ -.](\d{4})","($1) $2-$3"))

Then it comes back with an error. #ERROR is displayed on my report. Can you use iif with a regex? Is there another way?

Answer 1

(I'm assuming that you're only getting #ERROR for your null-valued records, since that's what happened when I set up a test case using the expression you provided. If not, I'll investigate further.)

You certainly can use a regex inside of the IIF() statement, but it evaluates both result conditions regardless of the result of your test expression. Therefore, your Regex.replace() is performed on your NULL values as well, which naturally generates an error.

I was able to produce the behaviour I think that you're after by rearranging things a little, so that we have this expression instead (formatted for readability):

=System.Text.RegularExpressions.Regex.Replace(
  IIf(IsNothing(Fields!Phone.Value), "", Fields!Phone.Value),
  "(\d{3})[ -.](\d{3})[ -.](\d{4})",
  "($1) $2-$3")
 )

That will give you a blank field when the value is NULL, otherwise it will format your telephone numbers accordingly. Hopefully that helps.