Skip to main content

Advanced Functions

This topic describes the advanced functions supported by StoneDB.

FunctionDescriptionExample
BIN(x)Returns the binary string equivalent to x.SELECT BIN(28);

->11100
BINARY(s)Converts string s to a binary string.SELECT BINARY('STONEDB');

-> STONEDB
CASE expression
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
WHEN conditionN THEN resultN
ELSE result
END		CASE specifies the start of the function and END specifies the end of the function.
If condition1 is met, result1 is returned. If condition2 is met, result2 is returned. If all conditions are not met, result is returned.
This function stops checking subsequent conditions immediately after a condition is met and returns the corresponding result.		SELECT CASE
WHEN 1>0
THEN '1 > 0'
WHEN 2>0
THEN '2 > 0'
ELSE '3 > 0'
END;

->1>0
CAST(x AS type)Converts the data type of x.SELECT CAST('2022-06-11' AS DATE);

->2022-06-11
COALESCE(expr1, expr2, ...., expr_n)Returns the first non-null value in the specified list.SELECT COALESCE(NULL, NULL,'CHINA', NULL, NULL,'STONEDB');

->CHINA
CONNECTION_ID()Returns the ID of the current connection.SELECT CONNECTION_ID();

->5
CONV(x,f1,f2)Converts x from base f1 to_ f2_.SELECT CONV(28,10,16);

->1C
CONVERT(s USING cs)Changes the character set of string s to character set cs.SELECT CHARSET('ABC');

->utf8

SELECT CHARSET(CONVERT('ABC' USING gbk));

->gbk
CURRENT_USER()Returns the current user.SELECT CURRENT_USER();

->root@localhost
DATABASE()Returns the name of the current database.SELECT DATABASE();

->test
IF(expr,v1,v2)Returns value v1 if expression expr is true or value v2 if expression expr is false.SELECT IF(1>10,'true','false') ;

->false
IFNULL(v1,v2)Returns value v1 if value v1 is not null. Otherwise, value v2 is returned.SELECT IFNULL(null,'Hello Word');

->HelloWord
ISNULL(expression)Checks whether expression is NULL.SELECT ISNULL(NULL);

->1
LAST_INSERT_ID()Returns the last AUTO_INCREMENT value.SELECT LAST_INSERT_ID();

->0
NULLIF(expr1, expr2)Compares two strings expr1 and expr2.
If they are the same, NULL is returned. Otherwise, expr1 is returned.		SELECT NULLIF(25,25);

->NULL
SESSION_USER()
SYSTEM_USER()
USER()		Returns the current user.SELECT SESSION_USER();

->root@localhost
VERSION()Returns the version number of the database.SELECT VERSION();

->5.6.24-StoneDB-log