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
Required: YesvalueTO_STRING
Required: YesvalueTO_BOOL
Required: YesvalueTO_ARRAY
Required: YesvalueVALUE
Required: YestextNUMBERVALUE
Required: Yestext
Required: Yesdecimal_separator
Required: Yesgroup_separator
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.
| Function | Returns | Behaviour | Form |
|---|---|---|---|
TO_NUMBER | decimal | The number the value holds | TO_NUMBER("42") |
TO_STRING | text | The value as text | TO_STRING(42) |
TO_BOOL | true or false | The value as true or false: true, yes, false, no in any case, or the numbers 1 and 0 | TO_BOOL("true") |
TO_ARRAY | list | A list holding the value | TO_ARRAY(1) |
VALUE | decimal | The number the text holds | VALUE("42") |
NUMBERVALUE | decimal | The number, reading the separators you name | NUMBERVALUE("1,234.5", ".", ",") |
Errors and exceptions
| Error | What happened | How to recover |
|---|---|---|
HTQL-5001 | The function was given the wrong number of arguments. The message names the function and the counts | Give it the arguments listed under Parameters |
HTQL-1004 Unsupported function | The name is not a function this engine runs | Check the name and its spelling against this entry |
HTQL-5004 | The value is missing | Give the function a value |
HTQL-2001 | The value cannot become the type | Check the value, or test it first |
HTQL-8003 | VALUE was given text that is not a number | Pass the text of a number |
Limitations
- A value that cannot become the type is refused:
TO_NUMBER("abc")andTO_NUMBER("1,234")answerHTQL-2001"Cannot convert … to number", andTO_BOOL("maybe")answersHTQL-2001. - A null becomes 0 with
TO_NUMBER, empty text withTO_STRINGand a list holding null withTO_ARRAY. TO_STRINGof a list joins its items without a separator,TO_STRING([1, 2])is "12", andTO_STRINGof a record is empty text.NUMBERVALUEneeds 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.