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 LEAST(1,2,3) FROM Employee |
IFNULL
IFNULL(a, b)
Returns the value of 'a' if it is not null, otherwise 'b'.
...
| Code Block |
|---|
SELECT ROWNUM(), * FROM Employee |