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.

Aggregate Functions New in 17.0

Numeric Functions New in 17.0

String Functions New in 17.0

Time and Date Functions New in 17.0

...

Aggregate Functions (New in 17.0

...

)

Aggregate Functions

AVG

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

...

COUNT

Returns the count of all rows, or of non-null values.

Example

SELECT COUNT(*) FROM Employee
GO
SELECT COUNT(ID) FROM Employee
GO

...

Back to top

MAX

Returns the highest value.

Example

SELECT MAX(Salary) FROM Employee

...

Back to top

MIN

Returns the lowest value.

Example

SELECT MIN(Salary) FROM 

...

Employee

SUM

Returns the sum of all values.

Example

SELECT SUM(Salary) FROM Employee

Back to top

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.

Example

SELECT GROUP_CONCAT(First_name ORDER BY E_id SEPARATOR ';') FROM Employee

...

SELECTIVITY

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. 

ExampleExample

SELECT SELECTIVITY(First_name), SELECTIVITY(City) FROM Employee WHERE ROWNUM() < 2

Back to top

BOOL_AND, BOOL_OR

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

Back to top

STDDEV_POP, STDDEV_SAMP

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, VAR_SAMP

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

Back to top

Numeric Functions (New in 17.0)

ABS

Returns the absolute value of the numeric expression.

Example

SELECT ABS(Salary) FROM Employee

...

ACOS

Returns the arc cosine.

Example

SELECT ACOS(D) FROM Employee

...

Back to top

ASIN

Returns the arc sine.

Example

SELECT ASIN(D) FROM Employee

...

ATAN

Calculates the arc tangent.

ExampleExample

SELECT ATAN(D) FROM Employee

...

Back to top

COS, COSH

COS returns the trigonometric cosine whereas COSH returns the hyperbolic cosine.

Example

SELECT COS(ANGLE) FROM Employee

...

Example

SELECT COSH(X) FROM Employee

...

Back to top

COT

Calculates the trigonometric cotangent.

Example

SELECT COT(ANGLE) FROM Employee

...

Back to top

SIN, SINH

SIN returns the trigonometric sine whereas SINH returns the hyperbolic sine.

Example

SELECT SIN(ANGLE) FROM Employee

...

Example

SELECT SINH(ANGLE) FROM Employee

...

Back to top

TAN, TANH

TAN calculates the trigonometric tangent whereas TANH calculates the hyperbolic tangent.

Example

SELECT TAN(ANGLE) FROM Employee

...

Example

SELECT TANH(X) FROM Employee

...

Back to top

ATAN2

Calculates the angle when converting the rectangular coordinates to polar coordinates.

Example

SELECT ATAN2(X,Y) FROM Employee

...

Back to top

MOD

The modulo operation. This method returns a long.

Example

SELECT MOD(Salary, Deduction) FROM Employee

...

Back to top

CEILING

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

...

DEGREES

Converts an angle measured in radians to an approximately equivalent angle measured in degrees.

Example

SELECT DEGREES(A) FROM Employee

...

Back to top

EXP

Returns Euler's number e raised to the power of a numeric value.

Example

SELECT EXP(LOG(20)), LOG(EXP(20)) FROM Employee

...

FLOOR

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

...

Back to top

LOG, LOG10

LOG returns the natural logarithm whereas LOG10 returns the base-10 logarithm.

Example

SELECT LOG(ID) FROM Employee

Example

SELECT LOG10(ID) FROM Employee

...

Back to top

RADIANS

Converts an angle measured in degrees to an approximately equivalent angle measured in radians.

Example

SELECT RADIANS(A) FROM Employee

...

Back to top

SQRT

Returns the square root of a numerical value.

Example

SELECT SQRT(Salary) FROM Employee

...

Back to top

PI

Returns the value of PI.

Example

SELECT PI() FROM Employee

...

POWER

Returns the base to the exponent power of a numerical value.

Example

SELECT POWER(ID,3) FROM Employee

...

Back to top

RAND

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()

Back to top

RANDOM_UUID

Returns a new UUID with 122 pseudo random bits. 

ExampleExample

SELECT * FROM Employee ORDER BY RANDOM_UUID()

...

ROUND

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 

Back to top

SECURE_RAND

Generates a number of cryptographically secure random numbers. This method returns bytes.

Example

SELECT SECURE_RAND(16) FROM Employee

...

Back to top

SIGN

Returns -1 if the value is smaller than 0, 0 if zero, and otherwise 1.

Example

SELECT SIGN(ID) FROM Employee

...

ENCRYPT

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

...

Back to top

DECRYPT

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

...

HASH

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

Back to top

TRUNCATE

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

Back to top

COMPRESS

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

Back to top

EXPAND

Expands data that was compressed using the COMPRESS function. This method returns bytes.

Example

SELECT UTF8TOSTRING(EXPAND(COMPRESS(STRINGTOUTF8('Jon')))) FROM Employee

...

ZERO

Returns the value 0.

Example

SELECT ZERO() FROM Employee

Back to top

String Functions (New in 17.0)

ASCII

Returns the ASCII value of the first character in the string. This method returns an int.

Example

SELECT Fname, ASCII(Fname) FROM Employee

...

BIT_LENGTH

Returns the number of bits in a string.

Example

SELECT Fname, BIT_LENGTH(Fname) FROM Employee

...

LENGTH

Returns the number of characters in a string.

Example

SELECT City, LENGTH(City) FROM Employee

Back to top

CHAR

Returns the character that represents the ASCII value.

Example

SELECT CHAR(65) FROM Employee

...

CONCAT

CONCAT combines strings.

Example

SELECT CONCAT(Fname, SPACE(3), City) FROM Employee

Back to top

DIFFERENCE

Returns the difference between the sounds of two strings.

Example

SELECT DIFFERENCE(Fname, City) FROM Employee

Back to top

LOWER

Converts a string to lowercase. 

ExampleExample

SELECT LOWER(Fname) FROM Employee

Back to top

UPPER

Converts a string to uppercase.

Example

SELECT UPPER(Fname) FROM Employee

Back to top

LEFT

Returns the leftmost number of characters.

Example

SELECT LEFT(Fname, 2) FROM Employee

Back to top

RIGHT

Returns the rightmost number of characters.

Example

SELECT RIGHT(Fname, 2) FROM Employee

Back to top

LOCATE

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

POSITION(searchString, string)

Returns the location of a search string in a string. See also LOCATE.

Example

SELECT POSITION('J', Fname) FROM Employee

Back to top

LPAD, RPAD

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

Back to top

LTRIM, RTRIM

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

Back to top

TRIM

TRIM([option] [trimString] [FROM] string)

...

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

Back to top

REPEAT

REPEAT(string, int)

Returns a string repeated some number of times.

Example

SELECT REPEAT (Fname,3) FROM Employee WHERE Fname LIKE'T%'

Back to top

REPLACE

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, SUBSTR

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 

...

Back to top

TO_CHAR

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 

...

Back to top

SPACE

Returns a string consisting of a number of spaces.

Example

SELECT E_id,Fname,space(10),City FROM Employee 

Back to top

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.

Example

SELECT STRINGENCODE(STRINGDECODE('Lines 1\nLine 2')) FROM Employee 

Back to top

STRINGENCODE

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 

...

Back to top

STRINGTOUTF8

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 

...

Back to top

UTF8TOSTRING

Decodes a byte array in the UTF8 format to a string.

Example

SELECT UTF8TOSTRING(STRINGTOUTF8('This is a test')) FROM Employee 

...

Back to top

XMLATTR

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 

...

Back to top

XMLNODE

XMLNODE(elementString [, attributesString [, contentString [, indentBoolean]]])

Create an XML node element.

Example

SELECT XMLNODE('a', XMLATTR('href', 'http://h2database.com'), 'H2') FROM Employee 

...

XMLCOMMENT

XMLCOMMENT(commentString)

Creates an XML comment. Two dashes (--) are converted to - -. This method returns a string.

Example

SELECT XMLCOMMENT('Comment on XML') FROM Employee 

...

Back to top

XMLCDATA

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 

...

XMLSTARTDOC

Returns the XML declaration. The result is always <?xml version=1.0?>.

Example

SELECT XMLSTARTDOC() FROM Employee 

...

Back to top

XMLTEXT

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 

...

Back to top

Time and Date Functions (New in 17.0)

CURRENT_DATE

Returns the current date.

Example

SELECT CURRENT_DATE() FROM Employee

Back to top

CURRENT_TIME

Returns the current time.

Example

SELECT CURRENT_TIME() FROM Employee

Back to top

CURRENT_TIMESTAMP

Returns the current timestamp.

Example

SELECT CURRENT_TIMESTAMP() FROM Employee 

...

DATEADD

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 

Back to top

DATEDIFF

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 

Back to top

DAYNAME

Returns the name of the day (in English).

Example

SELECT DAYNAME('2001-01-31') FROM Employee

...

DAY_OF_MONTH

Returns the day of month (1-31).
 ExampleExample

SELECT DAY_OF_MONTH('2001-01-31') FROM Employee 

Back to top

DAY_OF_WEEK

Returns the day of the week (1 means Sunday).

Example

SELECT DAY_OF_WEEK('2001-01-31') FROM Employee 

Back to top

DAY_OF_YEAR

Returns the day of the year (1-366).

Example

SELECT DAY_OF_YEAR('2001-01-31') FROM Employee 

...

FORMATDATETIME

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

Back to top

HOUR, MINUTE, MONTH, YEAR

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

Back to top

More Functions (New in 17.0)

CAST

Converts a value to another data type.

Example

SELECT CAST(ID AS INT) FROM Employee

Back to top

COALESCE

Returns the first value that is not null.

Example

SELECT COALESCE(ID, Age, Salary) FROM Employee

Back to top

GREATEST

Returns the largest value that is not NULL, or NULL if all values are NULL.

Example

SELECT GREATEST(1, 2, 3) FROM Employee

Back to top

LEAST

Returns the smallest value that is not NULL, or NULL if all values are NULL.

Example

SELECT LEAST(1,2,3) FROM Employee

Back to top

IFNULL

IFNULL(a, b)

Returns the value of 'a' if it is not null, otherwise 'b'.

Example

SELECT SUM (IFNULL(Salary,100)) FROM Employee

...

NULLIF

NULLIF(a, b)

Returns NULL if 'a' is equals to 'b', otherwise 'a'.

Example

SELECT Fname,NULLIF(E_id ,Salary) FROM Employee

Back to top

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

SELECT NVL2(ID, 'not null', 'null') FROM Employee

Back to top

ROWNUM

Returns the number of the current row.

Example

SELECT ROWNUM(), * FROM Employee

Back to top