Type conversion functions

This section introduces type conversion functions used for report making by listing their descriptions, syntax, parameters, return values and options, and giving related examples.

bigint()

Description:

Convert a character type integer to a big integer, or obtain the integer part of numeric data and return a big integer.

The big integer is high precision data that supports greater-than-64-bit integers but has low computing efficiency.

For less-than-64-bit integers, long type data (support 64-bit integers which are 2⁶⁴ at most) or int type data (support 32-bit integers which are 2³² at most) are recommended.

Syntax:

bigint(stringExp)

bigint(numberExp)

Parameter:

stringExp	An integer string to be converted
numberExp	Numeric data whose integer part needs to be obtained; use decimal type when it is greater-than-64-bit data, while by default use double type (64-bit)

Return value:

Big integer

Example:

Example 1: bigint("123456789012345678901234567890") 

Return 123456789012345678901234567890

Example 2: bigint(decimal("1234567890123456789012345.67890")) 

Return 1234567890123456789012345

Example 3: bigint(12345.6789012345678901234567890) 

Return 12345

chn()

Description:

Convert an integer to capitalized Chinese characters for the corresponding number.

Syntax:

chn(intExp)

Parameter:

intExp

An integer expression

Return value:

Character type

Option:

@a	Enable to write a number in the way of “billion, ten thousand, thousand, hundred, ten” in Chinese; by default, write it in the way of “one, two, three, four, five, six, seven…” in Chinese
@u	Enable to write a number using capitalized Chinese characters, which are “壹贰叁肆伍陆柒捌玖拾佰仟”; by default, write it with the regular Chinese characters

Example:

Example 1: chn@a(1234567)  Return "一百二十三万四千五百六十七"

Example 2: chn(1234567)  Return "一二三四五六七"

Example 3: chn@au(1234567)  Return "壹佰贰拾叁万肆仟伍佰陆拾柒"

Example 4: chn@u(1234567)  Return "壹贰叁肆伍陆柒"

decimal()

Description:

Convert a string or a numeric value to a big decimal number.

Syntax:

decimal(stringExp)

decimal(numberExp)

Parameter:

stringExp	A specified numeric string, which is allowed to contain the decimal point
numberExp	A numeric value that is of or less than 64 bits; use stringExp when it has greater than 64 bits

Return value:

Big decimal number

Example:

Example 1: decimal("123456789012345678901234567890") 

Return 123456789012345678901234567890

Example 2: decimal(1234567890123456) 

Return 1234567890123456（BigDecimal type）

double()

Description:

Convert a string or a number to a 64-bit double-precision floating-point number.

Syntax:

double(string)

double(number)

Parameter:

string	The to-be-converted string expression of double-precision floating-point number, which is of or less than 64 bits; the function result is imprecise for a number greater than 64 bits
number	A to-be-converted double-precision floating-point number, which is of or less than 64 bits; the function result is imprecise for a number greater than 64 bits

Return value:

A 64-bit double-precision floating-point number

Example:

Example 1: double("1234567")  Return 1234567.0

Example 2: double(1234567)  Return 1234567.0

float()

Description:

Convert a single-precision floating-point number, a double-precision floating-point number or a string into a 64-bit single-precision floating-point type.

Syntax:

float(string)

float(number)

Parameter:

string	A to-be-converted string expression of single-precision floating-point number or double-precision floating-point number, which is of or less than 64 bits; result of float(string) is imprecise for a number greater than 64 bits
number	A to-be-converted number, which is of or less than 64 bits; the result of float(number) is imprecise for a number greater than 64 bits

Return value:

A 64-bit double-precision floating-point number

Example:

Example 1: float("1234567")  Return 1234567.0

Example 2: float(1234567.0)  Return 1234567.0

Example 3: float(1234567)  Report expression error because 1234567 isn’t a floating-point number

hexstring()

Description:

Convert the specified data or a byte array into hexadecimal characters (treat the data as unsigned data type).

Syntax:

hexstring(dataExp)

Parameter:

dataExp

The specified data or byte array expression

Option:

@s	Separate data with space; by default data will not be separated

Return value:

Hexadecimal characters

Example:

Example 1: hexstring@s(12345678)  Return "00 BC 61 4E"

Example 2: hexstring(12345678)  Return "00BC614E"

ifdate()

Description:

Find whether the specified data is date or datetime type.

Syntax:

ifdate(exp)

Parameter:

exp	An expression of any data type

Return value:

Boolean type

Example:

Example 1: ifdate("2006-10-10")  Return false

Example 2: ifdate(date("2006-10-10 10:20:30"))  Return true

Example 3: ifdate("20061010")  Return false

Example 4: ifdate("10:20:30")  Return false

Example 5: ifdate(date("2006-10-10"))  Return true

ifnumber()

Description:

Find whether the specified data is numeric type.

Syntax:

ifnumber(exp)

Parameter:

exp	An expression of any data type

Return value:

Boolean type

Example:

Example 1: ifnumber("abc")  Return false

Example 2: ifnumber("1234")  Return false

Example 3: ifnumber(1234)  Return true

Example 4: ifnumber("1234sss")  Return false

ifstring()

Description:

Find whether the specified data is string type.

Syntax:

ifstring( Exp )

Parameter:

Exp	An expression of any data type

Return value:

Boolean type

Example:

Example 1: ifstring("abc")  Return true

Example 2: ifstring(1234)  Return false

Example 3: ifstring("1980-01-01")   Return true

Example 4: ifstring(date("1980-01-01"))  Return false

iftime()

Description:

Find whether the specified data is time type.

Syntax:

iftime(exp)

Parameter:

exp	An expression of any data type

Return value:

Boolean type

Example:

Example 1: iftime("2006-10-10")  Return false

Example 2: iftime("2006-10-10 10:20:30")  Return false

Example 3: iftime("20061010")  Return false

Example 4: iftime("10:20:30")  Return false

Example 5: iftime(time("10:20:30"))  Return true

int()

Description:

A data type conversion function used to obtain the integer part of a given numeric value expression or a numeric string and convert its data type to 32-bit integer.

Syntax:

int(valueExp)

Parameter:

valueExp

An expression that must return a numeric value or a numeric string

Return value:

A 32-bit integer

Example:

Example 1: int("33")  Return 33

Example 2: int("33.999d")  Return 33

Example 3: int(1.5*1.5)  Return 2

Example 4: int(25.67)  Return 25

isempty()

Description:

Judge whether the number of elements in the result list of computing an expression is 0 or not, or whether the computing result is null or not.

Syntax:

isempty(exp)

Parameter:

exp	An expression

Return value:

Boolean type

Example:

Example 1: isdigit(A1)  Return true; now A1 is empty

Example 2: isdigit(A2)  Return false; now A2 has content

json()

Description:

Convert a data set or an expression to a JSON string.

Syntax:

json ('expression','Field1','Field2',...)

Parameter:

expression	The data set or expression to be converted to a JSON string
Field	The field name

Return value:

  JSON string

Option:

@d	Convert the specified data set to a JSON string in the format of [{Field:value, Field2:value...},{Field:value2, Field2:value2...},...]; when no Field is specified, convert all fields; when there is/are parameter Field(s), only convert the specified fields
@m	Take the specified data set as map to convert it to a JSON string in the format of {Field1Value:Field2Value, Field1Value2:Field2Value2}; when no Field* is specified, convert the first two fields only; when Field* is specified and when only two fields can be specified, they are interpreted as key and value of the map
@a	Convert the specified data set to a JSON string of two-dimensional array using all fields by default; when only one field is specified, return a one-dimensional array
@s	If the string object to be converted is already a JSON string, return the string directly without enclosing it with double quotation marks
@v	Convert value of computing the specified expression to a JSON string; without the option, convert record corresponding to the value of computing the specified expression to a JSON string
@r	When one of the above options is present, go to the current cell’s root set

Example:

ds1 data set:

	A
1	=to(1,5)
2	=json(A1)	[1,2,3,4,5]
3	['a':3,'b':4,'c':4]
4	=json(A3)	"['a':3,'b':4,'c':4]"
5	=json@s(A3)	['a':3,'b':4,'c':4]
6	=json@d("ds1","STUDENTID")	[{"STUDENTID":"Lily"},{"STUDENTID":"Rose"},{"STUDENTID":"Petter"},{"STUDENTID ":"Sira"},{"STUDENTID":"Lucy"}]
7	=json@m("ds1","STUDENTID","SCORE")	{"Lily":84,"Sira":84,"Petter":69,"Lucy":100,"Rose":77}
8	=json@m("ds1")	{"Class two":"Lucy","Class one":"Petter"}
9	=json@a("ds1","SCORE")	[84,77,69,84,100]
10	=json@v(ds1.select(#1))	["Class one","Class one","Class one","Class two","Class two"]

long()

Description:

Convert a string or a number to a 64-bit long integer.

Syntax:

long(string)

long(number)

Parameter:

string	The string expression you want to convert; its value must be a string that consists of a long integer which has 64 bits or less; for a value with more than 64 bits, the result of long(string) is imprecise and its fractional part, if any, will be truncated
number	A number; its value must be a long integer which has 64 bits or less; for a value with more than 64 bits, the result of long(number) is imprecise and its fractional part, if any, will be truncated

Return value:

A 64-bit long integer

Example:

Example 1: long("1234567")  Return 1234567

Example 2: long(1234567.789)  Return 1234567

number()

Description:

Convert a string to a 32-bit integer, 64-bit integer or 64-bit floating-point number.

Syntax:

number(string)

Parameter:

string

A string expression to be converted, whose value should be a numeric string

Return value:

A 32-bit integer, 64-bit integer or 64-bit floating-point number

Example:

Example 1: number("123")  Return 123

Example 2: number("123f")  Return 123.0

Example 3: number("123.45")   Return 123.45

Example 4: number("123.456d")  Return 123.456

rmb()

Description:

Convert a floating-point number or a decimal (two decimal places at most) to Chinese yuan written in capitalized characters.

Syntax:

rmb(numberExp)

Parameter:

numberExp

A data value expression

Return value:

Character type

Option:

@t	Write currency value in capitalized traditional Chinese characters

Example:

Example 1: rmb(10123456789.12)  Return "壹佰零壹亿贰仟叁佰肆拾伍万陆仟柒佰捌拾玖元壹角贰分"

Example 2: rmb@t(10123456789.12) Return "壹佰零壹億貳仟三佰肆拾伍萬陸仟柒佰捌拾玖元壹角貳分"

sequence()

Description:

Convert an array to a sequence.

Syntax:

sequence(list)

Parameter:

list

An array

Return value:

A sequence

Example:

Example 1: sequence( list(1,3,5,7,9))  Return [1,3,5,7,9]

string()

Description:

Convert an object to string type, during which formatting is permitted.

Syntax:

string(expression{, format}:loc)

Parameter:

expression	The constant object or the expression to be converted to a string
format	A format string used to format the result of parameter expression
loc	Language name, which is case-insensitive and applies only to datetime data; the most commonly used languages are Chinese (zh) and English (en); see esProc function A.sort() to know about other languages supported

Return value:

Character type

Option:

@q	Enclose the string specified in parameter expression in double quotes and ignore parameter format
@e	Escape the undisplayable character; represent the tab, carriage return, line break in string expression with the escape characters; if there are single quotes, double quotes or an escape character in the string, add an escape character before them; ignore parameter format when the option is present
@u	With @e option, if there is a character using large character set in string expression, convert the character to a Unicode one

Example:

Example 1:  string(123)

Return "123"

Example 2:  string(arg1,"yyyy MM dd")

arg1 is a date parameter whose value is 1972-09-09; the return value is "1972-09-09"

Example 3:  string(3456.9876,"￥#,##0.00")

Return "￥3,456.99"

Example 4:   string(5/6,"0.00%")

Return 83.33%

Example 5:   string@q(A5)

The content of A5 is the tab-separated a  b, a, b; the return value is "a b", where string a b is enclosed with the double quotation marks and the escape character is displayed

Example 6:   string@e(A6)

The content of A5 is the result of Example 5, and the expression returns \"a\tb\"; do not display the character tab after the string is escaped and add escape character to the double quotes

Example 7:   string@u(A8); the content of A8 is “中国”, and the return value is \u4E2D\u56FD。

Example 8:   string(date("2009-02-23"),"MMM dd,yyyy":"en") Return Feb 23,2009。