Page History
...
Numeric Functions
ABS
Returns the absolute value of the numeric expression.
...
SELECT ABS(Salary) FROM Employee
ACOS
Returns the arc cosine.
Example
SELECT ACOS(D) FROM Employee
ASIN
Returns the arc sine.
Example
SELECT ASIN(D) FROM EmployeeATAN
Calculates the arc tangent.
...
SELECT ATAN(D) FROM Employee
COS, COSH
COS returns the trigonometric cosine whereas COSH returns the hyperbolic cosine.
...
SELECT COSH(X) FROM Employee
COT
Calculates the trigonometric cotangent.
...
SELECT COT(ANGLE) FROM Employee
SIN, SINH
SIN returns the trigonometric sine whereas SINH returns the hyperbolic sine.
...
SELECT SINH(ANGLE) FROM Employee
TAN, TANH
TAN calculates the trigonometric tangent whereas TANH calculates the hyperbolic tangent.
...
SELECT TANH(X) FROM Employee
ATAN2
Calculates the angle when converting the rectangular coordinates to polar coordinates.
...
SELECT ATAN2(X,Y) FROM Employee
MOD
The modulo operation. This method returns a long.
...
SELECT MOD(Salary, Deduction) FROM Employee
CEILING
Returns the smallest integer greater than or equal to the specified numeric expression.
...
SELECT CEILING(Salary), CEILING(123.45), CEILING(0.0) FROM EmployeeDEGREES
Converts an angle measured in radians to an approximately equivalent angle measured in degrees.
...
SELECT DEGREES(A) FROM Employee
EXP
Returns Euler's number e raised to the power of a numeric value.
...
SELECT EXP(LOG(20)), LOG(EXP(20)) FROM Employee
FLOOR
Returns the largest integer less than or equal to the specified numeric expression.
...
SELECT FLOOR(Salary), FLOOR(-123.45), FLOOR(123.45) FROM Employee
LOG, LOG10
LOG returns the natural logarithm whereas LOG10 returns the base-10 logarithm.
...
SELECT LOG10(ID) FROM Employee
RADIANS
Converts an angle measured in degrees to an approximately equivalent angle measured in radians.
...
SELECT RADIANS(A) FROM Employee
SQRT
Returns the square root of a numerical value.
...
SELECT SQRT(Salary) FROM Employee
PI
Returns the value of PI.
Example
SELECT PI() FROM Employee
POWER
Returns the base to the exponent power of a numerical value.
...
SELECT POWER(ID,3) FROM Employee
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).
...
SELECT * FROM Employee ORDER BY RAND()RANDOM_UUID
Returns a new UUID with 122 pseudo random bits.
Example
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).
...
SELECT First_name, ROUND(Salary, 1) FROM Employee SECURE_RAND
Generates a number of cryptographically secure random numbers. This method returns bytes.
...
SELECT SECURE_RAND(16) FROM Employee
SIGN
Returns -1 if the value is smaller than 0, 0 if zero, and otherwise 1.
...
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.
...
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.
...
SELECT DECRYPT('AES', '00', STRINGTOUTF8('Test')) FROM EmployeeHASH
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.
...
SELECT HASH('SHA256', STRINGTOUTF8('Password'), 1000) FROM EmployeeTRUNCATE
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.
...
SELECT TRUNCATE(E_id, 2) FROM EmployeeCOMPRESS
Compresses the data using the specified compression algorithm. Supported algorithms are: LZF (faster but lower compression; default), and DEFLATE (higher compression).
...
SELECT COMPRESS(STRINGTOUTF8('Jon')) FROM EmployeeEXPAND
Expands data that was compressed using the COMPRESS function. This method returns bytes.
...
SELECT UTF8TOSTRING(EXPAND(COMPRESS(STRINGTOUTF8('Jon')))) FROM EmployeeZERO
Returns the value 0.
Example
...
String Functions
ASCII
Returns the ASCII value of the first character in the string. This method returns an int.
...
SELECT Fname, ASCII(Fname) FROM EmployeeBIT_LENGTH
Returns the number of bits in a string.
...
SELECT Fname, BIT_LENGTH(Fname) FROM Employee
LENGTH
Returns the number of characters in a string.
...
SELECT City, LENGTH(City) FROM EmployeeCHAR
Returns the character that represents the ASCII value.
...
SELECT CHAR(65) FROM EmployeeCONCAT
CONCAT combines strings.
Example
SELECT CONCAT(Fname, SPACE(3), City) FROM Employee
DIFFERENCE
Returns the difference between the sounds of two strings.
...
SELECT DIFFERENCE(Fname, City) FROM Employee
LOWER
Converts a string to lowercase.
Example
SELECT LOWER(Fname) FROM Employee
UPPER
Converts a string to uppercase.
...
SELECT UPPER(Fname) FROM Employee
LEFT
Returns the leftmost number of characters.
...
SELECT LEFT(Fname, 2) FROM Employee
RIGHT
Returns the rightmost number of characters.
...
SELECT RIGHT(Fname, 2) FROM Employee
LOCATE
LOCATE(searchString, string [, start_position])
...
SELECT Fname, LOCATE('le',Fname) FROM Employee
POSITION
POSITION(searchString, string)
...
SELECT POSITION('J', Fname) FROM Employee
LPAD, RPAD
LPAD(string, int [, paddingString])
RPAD(string, int [, paddingString])
...
SELECT RPAD(Fname,9,'*') FROM Employee
LTRIM, RTRIM
LTRIM removes all leading spaces from a string. RTRIM removes all trailing spaces from a string.
...
SELECT RTRIM(Fname),City FROM Employee
TRIM
TRIM([option] [trimString] [FROM] string)
...
SELECT TRIM(BOTH '_' FROM Fname) FROM Employee
REPEAT
REPEAT(string, int)
Returns a string repeated some number of times.
...
SELECT REPEAT (Fname,3) FROM Employee WHERE Fname LIKE'T%'
REPLACE
REPLACE(string, searchString [, replacementString])
...
SELECT REPLACE(Fname, ' ') FROM Employee
SUBSTRING, SUBSTR
SUBSTRING(string, startint [, lengthint])
...
SELECT SUBSTR('[Hello]', 1, 3) FROM Employee
TO_CHAR
TO_CHAR(value [, formatString] [, nlsParamString])
...
SELECT TO_CHAR(TIMESTAMP '2010-01-01 00:00:00', 'DD MON, YYYY') FROM Employee
SPACE
Returns a string consisting of a number of spaces.
...
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.
...
SELECT STRINGENCODE(STRINGDECODE('Lines 1\nLine 2')) FROM Employee
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.
...
SELECT STRINGENCODE(STRINGDECODE('Lines 1\nLine 2')) FROM Employee
STRINGTOUTF8
Encodes a string to a byte array using the UTF8 encoding format. This method returns bytes.
...
SELECT UTF8TOSTRING(STRINGTOUTF8('This is a test')) FROM Employee
UTF8TOSTRING
Decodes a byte array in the UTF8 format to a string.
...
SELECT UTF8TOSTRING(STRINGTOUTF8('This is a test')) FROM Employee
XMLATTR
XMLATTR(nameString, valueString)
...
SELECT XMLNODE('a', XMLATTR('p_name', 'http://h2database.com')) FROM Employee
XMLNODE
XMLNODE(elementString [, attributesString [, contentString [, indentBoolean]]])
...
SELECT XMLNODE('a', XMLATTR('href', 'http://h2database.com'), 'H2') FROM Employee
XMLCOMMENT
XMLCOMMENT(commentString)
...
SELECT XMLCOMMENT('Comment on XML') FROM Employee
XMLCDATA
XMLCDATA(valueString)
Creates an XML CDATA element. If the value contains ]]>, an XML text element is created instead. This method returns a string.
...
SELECT XMLCDATA('data') FROM Employee
XMLSTARTDOC
Returns the XML declaration. The result is always <?xml version=1.0?>.
...
SELECT XMLSTARTDOC() FROM Employee
XMLTEXT
XMLTEXT(valueString [,escapeNewlineBoolean])
...
Time and Date Functions
CURRENT_DATE
Returns the current date.
...
SELECT CURRENT_DATE() FROM EmployeeCURRENT_TIME
Returns the current time.
...
SELECT CURRENT_TIME() FROM EmployeeCURRENT_TIMESTAMP
Returns the current timestamp.
...
SELECT CURRENT_TIMESTAMP() FROM Employee DATEADD
DATEADD(unitString, addInt, timestamp)
...
SELECT DATEADD('MONTH', 1, DATE '2001-01-31') FROM Employee DATEDIFF
DATEDIFF(unitString, timestamp1, timestamp2)
...
SELECT DATEDIFF('YEAR', '2005-01-31', '2010-01-31') FROM Employee DAYNAME
Returns the name of the day (in English).
...
SELECT DAYNAME('2001-01-31') FROM EmployeeDAY_OF_MONTH
Returns the day of month (1-31).
Example
SELECT DAY_OF_MONTH('2001-01-31') FROM Employee DAY_OF_WEEK
Returns the day of the week (1 means Sunday).
...
SELECT DAY_OF_WEEK('2001-01-31') FROM Employee DAY_OF_YEAR
Returns the day of the year (1-366).
...
SELECT DAY_OF_YEAR('2001-01-31') FROM Employee FORMATDATETIME
FORMATDATETIME(timestamp, formatString [, localeString [, timezoneString]])
...
SELECT FORMATDATETIME(TIMESTAMP '2001-02-03 04:05:06', 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT') FROM EmployeeHOUR, 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.
...
More Functions
CAST
Converts a value to another data type.
...
SELECT CAST(ID AS INT) FROM EmployeeCOALESCE
Returns the first value that is not null.
...
SELECT COALESCE(ID, Age, Salary) FROM EmployeeGREATEST
Returns the largest value that is not NULL, or NULL if all values are NULL.
...
SELECT GREATEST(1, 2, 3) FROM EmployeeLEAST
Returns the smallest value that is not NULL, or NULL if all values are NULL.
...
SELECT LEAST(1,2,3) FROM EmployeeIFNULL
IFNULL(a, b)
Returns the value of 'a' if it is not null, otherwise 'b'.
...
SELECT SUM (IFNULL(Salary,100)) FROM EmployeeNULLIF
NULLIF(a, b)
Returns NULL if 'a' is equals to 'b', otherwise 'a'.
...
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.
...
SELECT NVL2(ID, 'not null', 'null') FROM EmployeeROWNUM
Returns the number of the current row.
...