Text formatting functions

Present a number or a template as text a reader can use.

Syntax

Write the function's name and the value to present, with the options it asks for.

DOLLAR(number, decimals)
FIXED(number, decimals, no_commas)
NUMBERVALUE(text, decimal_separator, group_separator)
HANDLEBARS(template, data, [mode])
TEXT(number, format)
SPELLNUMBER(number)
ARRAYTOTEXT(array, [format])

Parameters and arguments

Arguments are written in brackets, separated by commas. An argument in square brackets is optional.

  • DOLLAR

    number

    Required: Yes
  • decimals

    Required: Yes
  • FIXED

    number

    Required: Yes
  • decimals

    Required: Yes
  • no_commas

    Required: Yes
  • NUMBERVALUE

    text

    Required: Yes
  • decimal_separator

    Required: Yes
  • group_separator

    Required: Yes
  • HANDLEBARS

    template

    Required: Yes
  • data

    Required: Yes
  • mode

    Required: No
  • TEXT

    number

    Required: Yes
  • format

    Required: Yes
  • SPELLNUMBER

    number

    Required: Yes
  • ARRAYTOTEXT

    array

    Required: Yes
  • format

    Required: No

Examples

A computed line total, a refund and a round figure, each shown as currency.

Return type and behaviour

Each function returns one value. The table shows its type, what it does and one way to write it; the examples use the functions in realistic statements.

FunctionReturnsBehaviourForm
DOLLARtextThe amount as currency with a dollar sign and thousands separatorsDOLLAR(1234.5, 2)
FIXEDtextThe value as text with a fixed number of decimalsFIXED(1234.567, 1, false)
NUMBERVALUEdecimalThe number the text holdsNUMBERVALUE("1,234.5", ".", ",")
HANDLEBARStextThe template with each name filled inHANDLEBARS("Hi {{name}}", {"name": "Ada"})
TEXTtextThe number written in the formatTEXT(1234.5, "#,##0.00")
SPELLNUMBERtextThe number in words. A decimal is written as dollars and centsSPELLNUMBER(1250)
ARRAYTOTEXTtextThe list written out as textARRAYTOTEXT([1, 2, 3])
Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-5001The function was given the wrong number of arguments. The message names the function and the countsGive it the arguments listed under Parameters
HTQL-1004 Unsupported functionThe name is not a function this engine runsCheck the name and its spelling against this entry
HTQL-5004A required argument is missing, such as DOLLAR(1234.5)Give the decimals
HTQL-5005DOLLAR or FIXED was given a whole numberWrite the amount with a decimal point, such as 1234.0
HTQL-2001HANDLEBARS was given a mode other than html or textUse html or text
Limitations
  • DOLLAR and FIXED return text, not numbers. Do the arithmetic first and format last.
  • DOLLAR and FIXED need the amount as a decimal: DOLLAR(1234, 0) is refused with HTQL-5005, DOLLAR(1234.0, 0) is not.
  • NUMBERVALUE needs all three arguments: with only the text it stops with the message "Out of bounds access". Coming: reading a number that uses a comma for the decimal point, NUMBERVALUE("1.234,5", ",", "."), which answers 1.2345 where 1234.5 is meant. The form with a point for the decimal, NUMBERVALUE("1,234.5", ".", ","), answers 1234.5.
  • Coming: a variable that holds a record with a single field, passed to HANDLEBARS, fills nothing: after LET order = {"name": "Ada"}, HANDLEBARS("Hi {{name}}", order) answers "Hi ". A record with two or more fields, or a record written in the call, works.
Permissions and security

These functions read nothing from your tables and write nothing.

When a function is applied to a column or a variable, the statement that reads the data needs read access, as its own entry says.

Best practices
  • Format a value only when you present it.
  • Use HANDLEBARS for messages with several values.