Page History
This document details Aqua Data Studio's Excel SQL syntax and functions. Supported functions include Aggregate Functions, Numeric Functions, String Functions, Time and Date Functions.
Anchor | ||||
---|---|---|---|---|
|
Table of Contents | ||
---|---|---|
|
Aggregate Functions (New in 17.0)
...
Code Block |
---|
SELECT SUM(Salary) FROM Employee |
GROUP_CONCAT
Concatenates strings with a separator. The default separator is a ',' (without space). This method returns a string. If no rows are selected, the result is NULL
. Aggregates are only allowed in select statements.
...
Estimates the selectivity (0-100) of a value. The value is defined as (100 * distinctCount / rowCount). The selectivity of 0 rows is 0 (unknown). Up to 10000 values are kept in memory. Aggregates are only allowed in select statements.
Example
Code Block |
---|
SELECT SELECTIVITY(First_name), SELECTIVITY(City) FROM Employee WHERE ROWNUM() < 2 |
...
Code Block |
---|
SELECT VAR_SAMP(ID) FROM Employee |
Numeric Functions (New in 17.0)
...
Code Block |
---|
SELECT COSH(X) FROM Employee |
COT
Calculates the trigonometric cotangent.
...
Code Block |
---|
SELECT MOD(Salary, Deduction) FROM Employee |
CEILING
Returns the smallest integer greater than or equal to the specified numeric expression.
...
Code Block |
---|
SELECT LOG(ID) FROM Employee
|
Example
Code Block |
---|
SELECT LOG10(ID) FROM Employee |
RADIANS
Converts an angle measured in degrees to an approximately equivalent angle measured in radians.
...
Code Block |
---|
SELECT * FROM Employee ORDER BY RAND() |
RANDOM_UUID
Returns a new UUID with 122 pseudo random bits.
...
Code Block |
---|
SELECT ENCRYPT('AES', '00', STRINGTOUTF8('Test')) FROM Employee |
DECRYPT
Decrypts data using a key. The supported algorithm is AES. The block size is 16 bytes. This method returns bytes.
...
Code Block |
---|
SELECT ZERO() FROM Employee |
String Functions (New in 17.0)
...
Code Block |
---|
SELECT CONCAT(Fname, SPACE(3), City) FROM Employee |
DIFFERENCE
Returns the difference between the sounds of two strings.
...
Converts a string to lowercase.
Example
Code Block |
---|
SELECT LOWER(Fname) FROM Employee |
...
Code Block |
---|
SELECT RIGHT(Fname, 2) FROM Employee |
LOCATE
LOCATE(searchString, string [, start_position])
...
Returns the location of a search string in a string. See also LOCATE.
Example
Code Block |
---|
SELECT POSITION('J', Fname) FROM Employee |
LPAD, RPAD
LPAD(string, int [, paddingString])
RPAD(string, int [, paddingString])
...
Code Block |
---|
SELECT TRIM(BOTH '_' FROM Fname) FROM Employee |
REPEAT
REPEAT(string, int)
...
Code Block |
---|
SELECT E_id,Fname,space(10),City FROM Employee |
STRINGDECODE
Converts a encoded string using the Java string literal encoding format. Special characters are \b, \t, \n, \f, \r, \", \\, \<octal>, \u<unicode>. This method returns a string.
...
Code Block |
---|
SELECT XMLNODE('a', XMLATTR('p_name', 'http://h2database.com')) FROM Employee |
XMLNODE
XMLNODE(elementString [, attributesString [, contentString [, indentBoolean]]])
...
Code Block |
---|
SELECT XMLTEXT('Text data') FROM Employee |
Time and Date Functions (New in 17.0)
...
Code Block |
---|
SELECT DATEDIFF('YEAR', '2005-01-31', '2010-01-31') FROM Employee |
DAYNAME
Returns the name of the day (in English).
...
Code Block |
---|
SELECT FORMATDATETIME(TIMESTAMP '2001-02-03 04:05:06', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT') FROM Employee |
HOUR, MINUTE, MONTH, YEAR
...
Code Block |
---|
SELECT HOUR('2001-02-03 04:05:06') FROM Employee |
Example
Code Block |
---|
SELECT MINUTE('2001-02-03 04:08:06') FROM Employee |
Example
Code Block |
---|
SELECT MONTH('2001-05-03') FROM Employee |
Example
Code Block |
---|
SELECT YEAR('2045-02-03') FROM Employee |
More Functions (New in 17.0)
...
Converts a value to another data type.
Example
Code Block |
---|
SELECT CAST(ID AS INT) FROM Employee |
COALESCE
Returns the first value that is not null.
Example
Code Block |
---|
SELECT COALESCE(ID, Age, Salary) FROM Employee |
...
GREATEST
Returns the largest value that is not NULL, or NULL if all values are NULL.
Example
Code Block |
---|
SELECT GREATEST(1, 2, 3) FROM Employee |
LEAST
Returns the smallest value that is not NULL, or NULL if all values are NULL.
Example
Code Block |
---|
SELECT LEAST(1,2,3) FROM Employee |
IFNULL
IFNULL(a, b)
Returns the value of 'a' if it is not null, otherwise 'b'.
Example
Code Block |
---|
SELECT SUM (IFNULL(Salary,100)) FROM Employee |
...
NULLIF
NULLIF(a, b)
Returns NULL if 'a' is equals to 'b', otherwise 'a'.
Example
Code Block |
---|
SELECT Fname,NULLIF(E_id ,Salary) FROM Employee |
...
NVL2
NVL2(testValue, a, b)
If the test value is null, then 'b' is returned. Otherwise, 'a' is returned. The data type of the returned value is the data type of 'a' if this is a text type.
Example
Code Block |
---|
SELECT NVL2(ID, 'not null', 'null') FROM Employee |
ROWNUM
Returns the number of the current row.
Example
Code Block |
---|
SELECT ROWNUM(), * FROM Employee |