Standard functions

Read（3） Like（0） Label: standard functions,

DQL query syntax supports the following functions:

String functions

Standard functions	Description
LOWER(str)	Convert into lower case
UPPER(str)	Convert into upper case
LTRIM(str)	Delete whitespaces on the leftmost side
RTRIM(str)	Delete whitespaces on the rightmost side
TRIM(str)	Delete whitespaces on both sides
SUBSTR(str,start,len) SUBSTR(str,start)	Return a substring
LEN(str)	Return the length of a string
INDEXOF(sub,str[,start])	Return the position of a substring
LEFT(str,len)	Return the substring in the left of a specified string
RIGHT(str,len)	Return the substring in the right of a specified string
CONCAT(str1,str2)	Concatenate two strings
CONCAT(str1,str2,str3…)	Concatenate three or more strings
REPLACE(str,sub,rplc)	Replace a substring by another string

Datetime functions

Standard functions	Description
YEAR(d)	Return the year
MONTH(d)	Return the month
DAY(d)	Return the day
HOUR(d)	Return the hour
MINUTE(d)	Return the minute
SECOND(d)	Return the second
QUARTER(d)	Return the quarter
TODAY()	Return the current date
NOW()	Return the current time
ADDYEARS(d, n)	Add years
ADDMONTHS(d, n)	Add months
ADDDAYS(d, n)	Add days
ADDHOURS(d, n)	Add hours
ADDMINUTES(d, n)	Add minutes
ADDSECONDS(d, n)	Add seconds
DAYOFYEAR(d)	Return the day of year
WEEKOFYEAR(d)	Return the calendar week of the date

Numerical functions

Standard functions	Description
ABS(x)	Return absolute value
ACOS(x)	Return arc cosine
ASIN(x)	Return arc sine
ATAN(x)	Return arc tangent
ATAN2(x,y)	Return arc tangent
CEIL(x)	Return the smallest integer that is not less than x
COS(x)	Return cosine value
EXP(x)	Return the base of e to the power of x
FLOOR(x)	Return the biggest integer that not greater than x
LN(x)	Return the natural logarithm
LOG10(x)	Return the logarithm with base 10
MOD(x,m)	Return the remainder of x divided by m
POWER(x,y)	Return the value of x raised to the power of y
ROUND(x,n)	Return x rounded to n digits from the decimal point
SIGN(x)	Return the sign for x
SIN(x)	Return the sine value
SQRT(x)	Return the square root of x
TAN(x)	Return the tangent value
TRUNC(x,n)	Return x truncated to n decimal places
RAND(seed)	Return a random number

Conversion functions

Standard functions	Description
ASCII(str)	Return numeric value of left-most character
CHR(n)	Return the character for integer n
INT(x)	Return an integer for string or number x
DECIMAL(x,len,scale)	Return the numeric value for a string or number
TIMESTAMP(str)	Convert the format string of yyyy-mm-dd hh24:mi:ss into datetime
NUMTOCHAR(d)	Return a string for number d
DATETOCHAR(date)	Convert date into a string in the format of yyyy-mm-dd hh24:mi:ss

Other functions

Standard functions	Description
NULLIF(x1,x2)	Return null if x1=x2, otherwise return x1
COALESCE(x1,…)	Return the first non-null parameter