Conversion functions

Turn a value into the type you need.

Syntax

Write the function's name and the value.

TO_NUMBER(value)
TO_STRING(value)
TO_BOOL(value)
TO_ARRAY(value)
VALUE(text)
NUMBERVALUE(text, decimal_separator, group_separator)

Parameters and arguments

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

  • TO_NUMBER

    value

    Required: Yes
  • TO_STRING

    value

    Required: Yes
  • TO_BOOL

    value

    Required: Yes
  • TO_ARRAY

    value

    Required: Yes
  • VALUE

    text

    Required: Yes
  • NUMBERVALUE

    text

    Required: Yes
  • decimal_separator

    Required: Yes
  • group_separator

    Required: Yes

Examples

A spreadsheet row where everything arrived as text: the quantity and price are multiplied and the paid flag is read as true or false.

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
TO_NUMBERdecimalThe number the value holdsTO_NUMBER("42")
TO_STRINGtextThe value as textTO_STRING(42)
TO_BOOLtrue or falseThe value as true or false: true, yes, false, no in any case, or the numbers 1 and 0TO_BOOL("true")
TO_ARRAYlistA list holding the valueTO_ARRAY(1)
VALUEdecimalThe number the text holdsVALUE("42")
NUMBERVALUEdecimalThe number, reading the separators you nameNUMBERVALUE("1,234.5", ".", ",")
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-5004The value is missingGive the function a value
HTQL-2001The value cannot become the typeCheck the value, or test it first
HTQL-8003VALUE was given text that is not a numberPass the text of a number
Limitations
  • A value that cannot become the type is refused: TO_NUMBER("abc") and TO_NUMBER("1,234") answer HTQL-2001 "Cannot convert … to number", and TO_BOOL("maybe") answers HTQL-2001.
  • A null becomes 0 with TO_NUMBER, empty text with TO_STRING and a list holding null with TO_ARRAY.
  • TO_STRING of a list joins its items without a separator, TO_STRING([1, 2]) is "12", and TO_STRING of a record is empty text.
  • NUMBERVALUE needs all three arguments. Coming: NUMBERVALUE("1.234,5", ",", ".") answers 1.2345 where 1234.5 is meant; the form with a point for the decimal, NUMBERVALUE("1,234.5", ".", ","), answers 1234.5.
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
  • Convert at the edge of a statement, where the data first arrives.
  • Use NUMBERVALUE for numbers written with commas or other separators.