DML functions: Formatting

FomatString

formatString (date)

formatString (integer)

formatString (real)

FomatString

When you map any data class (except Very Large Object) to a String, the conversion is implicit. This means that the software converts the Data Class automatically without you needing to call a conversion Function.

However, if you want to convert a data class to a String and you want to format that data differently, you must use the formatString Functions.

formatString <dates>

formatString Comment

Description

formatString converts the value of a Date expression to a String using the format that you specify.

Syntax

formatString(strong> Date_expression, format)

Return

String

Parameters

Date_expression
Enter an expression that returns a Date value.

format
Specify the format of the return String.

Authorized characters

To specify a format for the String that formatString returns, use the following characters:
  • C - century
  • Y - year
  • M - month
  • D - day
  • h - hour
  • m - minutes
  • s - seconds
  • uh - hours difference between the local time and UTC (Coordinated Universal Time)
  • um - minutes difference between the local time and UTC (Coordinated Universal Time)

 

Also, to improve readability, you can use any of the following characters, that formatString inserts into the returned String:
  • - (hyphen)
  • / (slash)
  • , (comma)
  • . (period)
  • : (colon)
  • ; (semi-colon)
  • 'text'

 

Examples

In the following example, formatString converts to String the date expression provided in the first parameter, and formats it according to the second parameter.

formatString('2000-03-30', "DD/MM/YY") returns "30/03/00"

formatString)'2000-03-30 08:03:20', "CCYY MM DD 'at' hh'H' mm'mn' ss's'") returns "2000 03 30 at 08H 03mn 20s".

Error

formatString('2000-03-30', "DD/MM/Y") - the format is incorrect if a single Y is present.

formatString <integers>

formatString Comment

Description

formatString converts the value of an Integer expression to a String, using the format that you specify.

Syntax

formatString(Integer_expression, format [,thousands_separator])

Return

String

Parameters

Integer_expression
Enter an expression that returns an
Integer value.

format
Specify the format of the return
String.

thousands_separator
Specify a character for the thousands separator used in the
String. This parameter is optional and by default is represented by a space. The Lg separator must be 1.

Authorized characters

To specify the format that formatString returns, use the following characters:
  • G: thousands separator
  • S: positive or negative sign
  • s: negative sign for negative values and space for positive values
  • E: exponent
  • e: exponent
  • #: significant number
  • 0: significant number or non-significant number

 

Also, to improve readability, you can use any of the following characters, that formatString inserts into the returnedString:
  • -: negative sign
  • +: positive sign
  • $
  • space

Examples

formatString(83154, "000000") returns "083154"

formatString(83154, "######") returns "83154"

formatString(83154, "+##G###") returns "+83 154"

formatString(3154, "$#####") returns "$3154"

formatString <real>

formatString Comment

Description

formatString returns the value of the Real number as a String using the format that you specify.

Syntax

formatString(Real_number_expression, [format, decimal_separator [,thousands_separator]])

Return

String

Parameters

Real_number_expression
Enter an expression that returns a Real number

format
Specify the format of the return String. This parameter is optional and if it is not present, the conversion keeps the number of decimals, the sign, and the decimal separator of the real number given as a parameter.

The result should be similar to a real-to-real number mapping with all the keeps activated. An except exists for numbers in the interval [-1,1] for which:

  • A zero is added before the decimal separator in the case of negative numbers or positive numbers without the + sign present.
  • If the + sign is present, the zero in the output is not present.

For example:

  • formatString(0.1) -> 0.1
  • formatString(-0.1) -> -0.1
  • formatString(+0.1) -> +.1

decimal_separator
Specify a character for the decimal separator used in the String. This parameter is optional and by default is represented by the period character (".").

thousands_separator
Specify a character for the thousands separator used in the String. This parameter is optional and by default is represented by a space.

Authorized characters:

To specify the format of the value that formatString returns, use the following characters:
  • D: decimal separator
  • G: thousands separator
  • S: positive or negative sign
  • s: negative sign for negative values and space for positive values
  • E: exponent
  • e: exponent
  • #: significant number
  • 0: significant number or non-significant number
In order to improve readability, you can also use any of the following characters, that formatStringinserts into the returned String:
  • -: negative sign
  • +: positive sign
  • $
  • space

Examples

formatString(8315.4, "00000D00") returns "08315.40"

formatString(8315.4, "#####D##") returns "8315.4"

formatString(8315.4, "##G##0D00") returns "8 315.40"

formatString(315.4, "$##0D00") returns "$315.40"

formatString(+315.400 ) returns "+315.400"

Related topics

Conversion functions

Related Links