Versions Compared

Old Version 15

changes.mady.by.user Nadine Hinojosa

Saved on

compared with

New Version Current

changes.mady.by.user Nadine Hinojosa

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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
backtotop
backtotop

Table of Contents
stylenone


Aggregate Functions (New in 17.0)

...

Code Block
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.

...

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

Back to top

Numeric Functions (New in 17.0)

...

Code Block
SELECT COSH(X) FROM Employee

Back to top

COT

Calculates the trigonometric cotangent.

...

Code Block
SELECT MOD(Salary, Deduction) FROM Employee

Back to top

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

Back to top

RADIANS

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

...

Code Block
SELECT * FROM Employee ORDER BY RAND()

Back to top

RANDOM_UUID

Returns a new UUID with 122 pseudo random bits.

...

Code Block
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.

...

Code Block
SELECT ZERO() FROM Employee

Back to top

String Functions (New in 17.0)

...

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

Back to top

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

Back to top

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 RTRIM(Fname),City FROM Employee

...

TRIM

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

Code Block
SELECT TRIM(BOTH '_' FROM Fname) FROM Employee

Back to top

REPEAT

REPEAT(string, int)

Returns a string repeated some number of times.

Example

Code Block
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

Code Block
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
 Code Block
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
 Code Block
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.
Example
 Code Block
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
 Code Block
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
 Code Block
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.
Example
 Code Block
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
 Code Block
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
 Code Block
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
 Code Block
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
 Code Block
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
 Code Block
SELECT XMLCDATA('data') FROM Employee 
...
XMLSTARTDOC
Returns the XML declaration. The result is always <?xml version=1.0?>.
Example
 Code Block
SELECT XMLSTARTDOC() FROM Employee 
...
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
 Code Block
SELECT XMLTEXT('Text data') FROM Employee 
...
Back to top
Time and Date Functions (New in 17.0)
...
Returns the current date.
Example
 Code Block
SELECT CURRENT_DATE() FROM Employee
Back to top
CURRENT_TIME
Returns the current time.
Example
 Code Block
SELECT CURRENT_TIME() FROM Employee
Back to top
CURRENT_TIMESTAMP
Returns the current timestamp.
Example
 Code Block
SELECT CURRENT_TIMESTAMP() FROM Employee 
Back to top
DATEADD
DATEADD(unitString, addInt, timestamp)
Adds units to a timestamp. The string indicates the unit. Use negative values to subtract units.
Example
 Code Block
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
 Code Block
SELECT DATEDIFF('YEAR', '2005-01-31', '2010-01-31') FROM Employee 
Back to top
DAYNAME
Returns the name of the day (in English).
Example
 Code Block
SELECT DAYNAME('2001-01-31') FROM Employee
Back to top
DAY_OF_MONTH
Returns the day of month (1-31).
 ExampleExample
 Code Block
SELECT DAY_OF_MONTH('2001-01-31') FROM Employee 
...
DAY_OF_WEEK
Returns the day of the week (1 means Sunday).
Example
 Code Block
SELECT DAY_OF_WEEK('2001-01-31') FROM Employee 
...
DAY_OF_YEAR
Returns the day of the year (1-366).
Example
 Code Block
SELECT DAY_OF_YEAR('2001-01-31') FROM Employee 
Back to top
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
 Code Block
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
 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
Back to top
More Functions (New in 17.0)
...
Converts a value to another data type.
Example
 Code Block
SELECT CAST(ID AS INT) FROM Employee
Back to top
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
Back to top
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
Back to top
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
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
 Code Block
SELECT NVL2(ID, 'not null', 'null') FROM Employee
Back to top
ROWNUM
Returns the number of the current row.
Example
 Code Block
SELECT ROWNUM(), * FROM Employee
Back to top