FORMAT-NUMBER Function

converting number strings in CSV and HL7 records from one format to another.

Purpose

Used for formatting number strings in CSV and HL7 records. For example, format “123.456” to the two-decimal number “123.46”.

For defining the output format, it uses the “standard numeric format strings” and also the “custom formatting strings” defined by Microsoft for .NET.

Format

FORMAT-NUMBER(source-data-element, output-format [, on-error-default])

The function takes 2 or 3 parameters, the last parameter specifies a default value of the conversion failed e.g. either because the formatting string is invalid, or the input value is unexpected.

Example 1

A TabularDataTransformer handler with the following config entry converts column #3 of the CSV input with padding 0’s to six digits. 

<Parameter>
  <!-- FORMAT-NUMBERE Example -->
  <Name>transformation-rule</Name>
  <Value>$3=>FORMAT-NUMBER($$,"000000", "000000")</Value>
</Parameter>
InputOutcome
c1,c2,123,c4,c5c1,c2,000123,c4,c5

Example 2

A TabularDataTransformer handler with the following config entry converts column #4 of the CSV input to number with 2 fixed decimal points. 

<Parameter>
  <!-- FORMAT-NUMBERE Example -->
  <Name>transformation-rule</Name>
  <Value>$4=>FORMAT-NUMBER($$,"0.00", "0.00")</Value>
</Parameter>
InputOutcome
c1,c2,c3,123.4,c5c1,c2,c3,123.40,c5

Related

More numeric formatting string examples. You may have to play around with some of the more fancy formattings.