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.
Returns the average (mean) value. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.
Example
SELECT AVG(Salary) FROM Employee |
Returns the count of all rows, or of non-null values.
Example
SELECT COUNT(*) FROM Employee GO SELECT COUNT(ID) FROM Employee GO |
Returns the highest value.
Example
SELECT MAX(Salary) FROM Employee |
Returns the lowest value.
Example
SELECT MIN(Salary) FROM Employee |
Returns the sum of all values.
Example
SELECT SUM(Salary) FROM Employee |
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.
Example
SELECT GROUP_CONCAT(First_name ORDER BY E_id SEPARATOR ';') FROM Employee |
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
SELECT SELECTIVITY(First_name), SELECTIVITY(City) FROM Employee WHERE ROWNUM() < 2 |
BOOL_AND returns true if all expressions are true. BOOL_OR returns true if any expression is true.
Example
SELECT BOOL_AND(ID > 100) FROM Employee |
Example
SELECT BOOL_OR(Name LIKE 'W%') FROM Employee |
STDDEV_POP returns the population standard deviation while STDDEV_SAMP returns the sample standard deviation.
Example
SELECT STDDEV_POP(DISTINCT ID) FROM Employee |
Example
SELECT STDDEV_SAMP(ID) FROM Employee |
VAR_POP returns the population variance while VAR_SAMP returns the sample variance.
Example
SELECT VAR_POP(ID) FROM Employee |
Example
SELECT VAR_SAMP(ID) FROM Employee |
Returns the absolute value of the numeric expression.
Example
SELECT ABS(Salary) FROM Employee |
Returns the arc cosine.
Example
SELECT ACOS(D) FROM Employee |
Returns the arc sine.
Example
SELECT ASIN(D) FROM Employee |
Calculates the arc tangent.
Example
SELECT ATAN(D) FROM Employee |
COS returns the trigonometric cosine whereas COSH returns the hyperbolic cosine.
Example
SELECT COS(ANGLE) FROM Employee |
Example
SELECT COSH(X) FROM Employee |
Calculates the trigonometric cotangent.
Example
SELECT COT(ANGLE) FROM Employee |
SIN returns the trigonometric sine whereas SINH returns the hyperbolic sine.
Example
SELECT SIN(ANGLE) FROM Employee |
Example
SELECT SINH(ANGLE) FROM Employee |
TAN calculates the trigonometric tangent whereas TANH calculates the hyperbolic tangent.
Example
SELECT TAN(ANGLE) FROM Employee |
Example
SELECT TANH(X) FROM Employee |
Calculates the angle when converting the rectangular coordinates to polar coordinates.
Example
SELECT ATAN2(X,Y) FROM Employee |
The modulo operation. This method returns a long.
Example
SELECT MOD(Salary, Deduction) FROM Employee |
Returns the smallest integer greater than or equal to the specified numeric expression.
Example
SELECT CEILING(Salary), CEILING(123.45), CEILING(0.0) FROM Employee |
Converts an angle measured in radians to an approximately equivalent angle measured in degrees.
Example
SELECT DEGREES(A) FROM Employee |
Returns Euler's number e raised to the power of a numeric value.
Example
SELECT EXP(LOG(20)), LOG(EXP(20)) FROM Employee |
Returns the largest integer less than or equal to the specified numeric expression.
Example
SELECT FLOOR(Salary), FLOOR(-123.45), FLOOR(123.45) FROM Employee |
LOG returns the natural logarithm whereas LOG10 returns the base-10 logarithm.
Example
SELECT LOG(ID) FROM Employee |
Example
SELECT LOG10(ID) FROM Employee |
Converts an angle measured in degrees to an approximately equivalent angle measured in radians.
Example
SELECT RADIANS(A) FROM Employee |
Returns the square root of a numerical value.
Example
SELECT SQRT(Salary) FROM Employee |
Returns the value of PI.
Example
SELECT PI() FROM Employee |
Returns the base to the exponent power of a numerical value.
Example
SELECT POWER(ID,3) FROM Employee |
Calling the function without parameter returns the next pseudo random number. Calling it with a parameter seeds the session's random number generator. This method returns a double between 0 (including) and 1 (excluding).
Example
SELECT * FROM Employee ORDER BY RAND() |
Returns a new UUID with 122 pseudo random bits.
Example
SELECT * FROM Employee ORDER BY RANDOM_UUID() |
Rounds to a number of digits, or to the nearest long if the number of digits is not set. This method returns a numeric (the same type as the input).
Example
SELECT First_name, ROUND(Salary, 1) FROM Employee |
Generates a number of cryptographically secure random numbers. This method returns bytes.
Example
SELECT SECURE_RAND(16) FROM Employee |
Returns -1 if the value is smaller than 0, 0 if zero, and otherwise 1.
Example
SELECT SIGN(ID) FROM Employee |
Encrypts data using a key. The supported algorithm is AES. The block size is 16 bytes. This method returns bytes.
Example
SELECT ENCRYPT('AES', '00', STRINGTOUTF8('Test')) FROM Employee |
Decrypts data using a key. The supported algorithm is AES. The block size is 16 bytes. This method returns bytes.
Example
SELECT DECRYPT('AES', '00', STRINGTOUTF8('Test')) FROM Employee |
Calculates the hash value using an algorithm, and repeats this process for a number of iterations. Currently, the only algorithm supported is SHA256. This method returns bytes.
Example
SELECT HASH('SHA256', STRINGTOUTF8('Password'), 1000) FROM Employee |
Truncates to a number of digits (to the next value closer to 0). This method returns a double. When used with a timestamp, truncates a timestamp to a date (day) value. When used with a date, truncates a date to a date (day) value less time part. When used with a timestamp as string, truncates a timestamp to a date (day) value.
Example
SELECT TRUNCATE(E_id, 2) FROM Employee |
Compresses the data using the specified compression algorithm. Supported algorithms are: LZF (faster but lower compression; default), and DEFLATE (higher compression).
Example
SELECT COMPRESS(STRINGTOUTF8('Jon')) FROM Employee |
Expands data that was compressed using the COMPRESS function. This method returns bytes.
Example
SELECT UTF8TOSTRING(EXPAND(COMPRESS(STRINGTOUTF8('Jon')))) FROM Employee |
Returns the value 0.
Example
SELECT ZERO() FROM Employee |
Returns the ASCII value of the first character in the string. This method returns an int.
Example
SELECT Fname, ASCII(Fname) FROM Employee |
Returns the number of bits in a string.
Example
SELECT Fname, BIT_LENGTH(Fname) FROM Employee |
Returns the number of characters in a string.
Example
SELECT City, LENGTH(City) FROM Employee |
Returns the character that represents the ASCII value.
Example
SELECT CHAR(65) FROM Employee |
CONCAT combines strings.
Example
SELECT CONCAT(Fname, SPACE(3), City) FROM Employee |
Returns the difference between the sounds of two strings.
Example
SELECT DIFFERENCE(Fname, City) FROM Employee |
Converts a string to lowercase.
Example
SELECT LOWER(Fname) FROM Employee |
Converts a string to uppercase.
Example
SELECT UPPER(Fname) FROM Employee |
Returns the leftmost number of characters.
Example
SELECT LEFT(Fname, 2) FROM Employee |
Returns the rightmost number of characters.
Example
SELECT RIGHT(Fname, 2) FROM Employee |
LOCATE(searchString, string [, start_position])
Returns the location of a search string in a string. If a start position is used, the characters before it are ignored. If position is negative, the rightmost location is returned. 0 is returned if the search string is not found.
Example
SELECT Fname, LOCATE('le',Fname) FROM Employee |
POSITION(searchString, string)
Returns the location of a search string in a string. See also LOCATE.
Example
SELECT POSITION('J', Fname) FROM Employee |
LPAD(string, int [, paddingString])
RPAD(string, int [, paddingString])
Left pad the string to the specified length. If the length is shorter than the string, it will be truncated at the end. If the padding string is not set, spaces will be used. The RPAD Right pad the string to the specified length. If the length is shorter than the string, it will be truncated. If the padding string is not set, spaces will be used. Example: RPAD(AMOUNT, 10, '*')
Example
SELECT LPAD(Fname,9,'@') FROM Employee |
Example
SELECT RPAD(Fname,9,'*') FROM Employee |
LTRIM removes all leading spaces from a string. RTRIM removes all trailing spaces from a string.
Example
SELECT LTRIM(Fname),City FROM Employee |
Example
SELECT RTRIM(Fname),City FROM Employee |
TRIM([option] [trimString] [FROM] string)
option - LEADING, TRAILING, BOTH
Removes all leading spaces, trailing spaces, or spaces at both ends, from a string. Other characters can be removed as well.
Example
SELECT TRIM(BOTH '_' FROM Fname) FROM Employee |
REPEAT(string, int)
Returns a string repeated some number of times.
Example
SELECT REPEAT (Fname,3) FROM Employee WHERE Fname LIKE'T%' |
REPLACE(string, searchString [, replacementString])
Replaces all occurrences of a search string in a text with another string. If no replacement is specified, the search string is removed from the original string. If any parameter is null, the result is null.
Example
SELECT REPLACE(Fname, ' ') FROM Employee |
SUBSTRING(string, startint [, lengthint])
Returns a substring of a string starting at a position. If the start index is negative, then the start index is relative to the end of the string. The length is optional. Also supported is: SUBSTRING(string [FROM start] [FOR length]).
Example
SELECT SUBSTR('[Hello]', 1, 3) FROM Employee |
TO_CHAR(value [, formatString] [, nlsParamString])
TO_CHAR function is used to format a timestamp, a number, or text.
Example
SELECT TO_CHAR(TIMESTAMP '2010-01-01 00:00:00', 'DD MON, YYYY') FROM Employee |
Returns a string consisting of a number of spaces.
Example
SELECT E_id,Fname,space(10),City FROM Employee |
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.
Example
SELECT STRINGENCODE(STRINGDECODE('Lines 1\nLine 2')) FROM Employee
|
Encodes special characters in a string using the Java string literal encoding format. Special characters are \b, \t, \n, \f, \r, \", \\, \<octal>, \u<unicode>. This method returns a string.
Example
SELECT STRINGENCODE(STRINGDECODE('Lines 1\nLine 2')) FROM Employee |
Encodes a string to a byte array using the UTF8 encoding format. This method returns bytes.
Example
SELECT UTF8TOSTRING(STRINGTOUTF8('This is a test')) FROM Employee |
Decodes a byte array in the UTF8 format to a string.
Example
SELECT UTF8TOSTRING(STRINGTOUTF8('This is a test')) FROM Employee |
XMLATTR(nameString, valueString)
Creates an XML attribute element of the form name=value. The value is encoded as XML text. This method returns a string.
Example
SELECT XMLNODE('a', XMLATTR('p_name', 'http://h2database.com')) FROM Employee |
XMLNODE(elementString [, attributesString [, contentString [, indentBoolean]]])
Create an XML node element.
Example
SELECT XMLNODE('a', XMLATTR('href', 'http://h2database.com'), 'H2') FROM Employee |
XMLCOMMENT(commentString)
Creates an XML comment. Two dashes (--) are converted to - -. This method returns a string.
Example
SELECT XMLCOMMENT('Comment on XML') FROM Employee |
XMLCDATA(valueString)
Creates an XML CDATA element. If the value contains ]]>, an XML text element is created instead. This method returns a string.
Example
SELECT XMLCDATA('data') FROM Employee |
Returns the XML declaration. The result is always <?xml version=1.0?>.
Example
SELECT XMLSTARTDOC() FROM Employee |
XMLTEXT(valueString [,escapeNewlineBoolean])
Creates an XML text element. If enabled, newline and linefeed is converted to an XML entity (&#). This method returns a string.
Example
SELECT XMLTEXT('Text data') FROM Employee |
Returns the current date.
Example
SELECT CURRENT_DATE() FROM Employee |
Returns the current time.
Example
SELECT CURRENT_TIME() FROM Employee |
Returns the current timestamp.
Example
SELECT CURRENT_TIMESTAMP() FROM Employee |
DATEADD(unitString, addInt, timestamp)
Adds units to a timestamp. The string indicates the unit. Use negative values to subtract units.
Example
SELECT DATEADD('MONTH', 1, DATE '2001-01-31') FROM Employee |
DATEDIFF(unitString, timestamp1, timestamp2)
Returns the the number of crossed unit boundaries between two timestamps.
Example
SELECT DATEDIFF('YEAR', '2005-01-31', '2010-01-31') FROM Employee |
Returns the name of the day (in English).
Example
SELECT DAYNAME('2001-01-31') FROM Employee |
Returns the day of month (1-31).
Example
SELECT DAY_OF_MONTH('2001-01-31') FROM Employee |
Returns the day of the week (1 means Sunday).
Example
SELECT DAY_OF_WEEK('2001-01-31') FROM Employee |
Returns the day of the year (1-366).
Example
SELECT DAY_OF_YEAR('2001-01-31') FROM Employee |
FORMATDATETIME(timestamp, formatString [, localeString [, timezoneString]])
Formats a date, time or timestamp as a string. The most important format characters are: y year, M month, d day, H hour, m minute, s second. Example: FORMATDATETIME(TIMESTAMP '2001-02-03 04:05:06','EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')
Example
SELECT FORMATDATETIME(TIMESTAMP '2001-02-03 04:05:06', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT') FROM Employee |
HOUR returns the hour (0 - 23) from a timestamp. MINUTE returns the minute (0 - 59). MONTH returns the month (1 - 12). YEAR returns the year from a timestamp.
Example
SELECT HOUR('2001-02-03 04:05:06') FROM Employee |
Example
SELECT MINUTE('2001-02-03 04:08:06') FROM Employee |
Example
SELECT MONTH('2001-05-03') FROM Employee |
Example
SELECT YEAR('2045-02-03') FROM Employee |
Converts a value to another data type.
Example
SELECT CAST(ID AS INT) FROM Employee |
Returns the first value that is not null.
Example
SELECT COALESCE(ID, Age, Salary) FROM Employee |
Returns the largest value that is not NULL, or NULL if all values are NULL.
Example
SELECT GREATEST(1, 2, 3) FROM Employee |
Returns the smallest value that is not NULL, or NULL if all values are NULL.
Example
SELECT LEAST(1,2,3) FROM Employee |
IFNULL(a, b)
Returns the value of 'a' if it is not null, otherwise 'b'.
Example
SELECT SUM (IFNULL(Salary,100)) FROM Employee |
NULLIF(a, b)
Returns NULL if 'a' is equals to 'b', otherwise 'a'.
Example
SELECT Fname,NULLIF(E_id ,Salary) FROM Employee |
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
SELECT NVL2(ID, 'not null', 'null') FROM Employee |
Returns the number of the current row.
Example
SELECT ROWNUM(), * FROM Employee |