Data Types

- + + + +Functions + + + +

Aggregate Functions

@@ -146,6 +28,12 @@ PERCENT_RANK
+ FIRST_VALUE
+ + LAST_VALUE
+ + NTH_VALUE
+ STDDEV_POP
STDDEV_SAMP
@@ -163,17 +51,23 @@ syntax-end --> MAX
MIN
- SUM
+ PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK
+ + FIRST_VALUE
+ + LAST_VALUE
+ NTH_VALUE
+ STDDEV_POP
STDDEV_SAMP
@@ -194,6 +88,8 @@ syntax-end --> RTRIM
+ LPAD
+ LENGTH
REGEXP_SUBSTR
@@ -223,14 +119,16 @@ syntax-end --> RTRIM
+ LPAD
+ LENGTH
REGEXP_SUBSTR
REGEXP_REPLACE
+ UPPER
- LOWER
@@ -251,6 +149,10 @@ syntax-end --> CURRENT_DATE
CURRENT_TIME
+ + CONVERT_TZ
+ + TIMEZONE_OFFSET

syntax-end --> @@ -259,12 +161,16 @@ syntax-end --> TO_DATE
- CURRENT_DATE
CURRENT_TIME
+ + CONVERT_TZ
+ + + TIMEZONE_OFFSET
@@ -313,6 +219,10 @@ syntax-end --> ARRAY_ELEM
ARRAY_LENGTH
+ + ANY
+ + ALL

syntax-end --> @@ -321,10 +231,14 @@ syntax-end --> ARRAY_ELEM
- ARRAY_LENGTH
+ + ANY
+ + ALL
+ @@ -338,6 +252,10 @@ syntax-end --> INVERT
+ ENCODE
+ + DECODE
+ COALESCE

syntax-end --> @@ -347,11 +265,15 @@ syntax-end --> MD5
- INVERT
+ ENCODE
+ + DECODE
+ + COALESCE
@@ -504,6 +426,60 @@ syntax-end -->

Example:

PERCENT_RANK( 100 ) WITHIN GROUP (ORDER BY X ASC)

FIRST_VALUE

+ +

+FIRST_VALUE( { expression } ) WITHIN GROUP (ORDER BY { expression } { ASC | DESC } )
+

FIRST_VALUE ( expression ) WITHIN GROUP ( ORDER BY expression

	`ASC`
	`DESC`

)

+ + +

The first value in each distinct group ordered according to the ORDER BY specification.

Example:

FIRST_VALUE( name ) WITHIN GROUP (ORDER BY salary DESC)

+ +

LAST_VALUE

+ +

+LAST_VALUE( { expression } ) WITHIN GROUP (ORDER BY { expression } { ASC | DESC } )
+

LAST_VALUE ( expression ) WITHIN GROUP ( ORDER BY expression

	`ASC`
	`DESC`

)

+ + +

The last value in each distinct group ordered according to the ORDER BY specification.

Example:

LAST_VALUE( name ) WITHIN GROUP (ORDER BY salary DESC)

+ +

NTH_VALUE

+ +

+NTH_VALUE( { expression, nthNumeric } ) WITHIN GROUP (ORDER BY { expression } { ASC | DESC } )
+

NTH_VALUE ( expression , nthNumeric ) WITHIN GROUP ( ORDER BY expression

	`ASC`
	`DESC`

)

+ + +

The nth value in each distinct group ordered according to the ORDER BY specification.

Example:

NTH_VALUE( name, 2 ) WITHIN GROUP (ORDER BY salary DESC)

STDDEV_POP

@@ -576,6 +552,42 @@ syntax-end -->
 Example:
 ARRAY_LENGTH(my_array_col)
ARRAY_LENGTH(ARRAY[1,2,3])
 
+ANY
+
++ANY( arrayTerm )
+
+
+ANY ( arrayTerm )
+
+
+
+Used on the right-hand side of a comparison expression to test that any array element satisfies the comparison expression against the left-hand side.
+Example:
+1 = ANY(my_array)
10 > ANY(my_array)
+
+ALL
+
++ALL( arrayTerm )
+
+
+ALL ( arrayTerm )
+
+
+
+Used on the right-hand side of a comparison expression to test that all array elements satisfy the comparison expression against the left-hand side. of the array.
+Example:
+1 = ALL(my_array)
10 > ALL(my_array)
+
 MD5
 
 @@ -612,6 +624,42 @@ syntax-end -->
 Example:
 INVERT(my_column)
 
+ENCODE
+
++ENCODE( expression, 'BASE62' )
+
+
+ENCODE ( expression , ' BASE62 ' )
+
+
+
+Encodes the expression according to the encoding format provided and returns the resulting string. For 'BASE62', converts the given base 10 number to a base 62 number and returns a string representing the number.
+Example:
+ENCODE(myNumber, 'BASE62')
+
+DECODE
+
++DECODE( expression, 'HEX' )
+
+
+DECODE ( expression , ' HEX ' )
+
+
+
+Decodes the expression according to the encoding format provided and returns the resulting value as a VARBINARY. For 'HEX', converts the hex string expression to its binary representation, providing a mechanism for inputting binary data through the console.
+Example:
+DECODE('000000008512af277ffffff8', 'HEX')
+
 COALESCE
 
 @@ -792,6 +840,24 @@ syntax-end -->
 Example:
 RTRIM('Hello   ')
 
+LPAD
+
++LPAD( stringTerm, lengthNumeric, [padString] )
+
+
+LPAD ( stringTerm , lengthNumeric ,   
padString )
+
+
+
+Pads the string expression with the specific pad character (space by default) up to the length argument.
+Example:
+LPAD('John',30)
+
 LENGTH
 
 @@ -972,188 +1038,43 @@ syntax-end -->
 Example:
 CURRENT_TIME()
 
+CONVERT_TZ
+
++CONVERT_TZ(dateTerm|timeTerm|timestampTerm, fromTimeZoneString, toTimeZoneString)
+
+
+ CONVERT_TZ ( dateTerm 
timeTerm 
timestampTerm , fromTimeZoneString , toTimeZoneString ) 
+
+
+
+Converts date/time from one time zone to another returning the shifted date/time value.
+Example:
+CONVERT_TZ(myDate, 'UTC', 'Europe/Prague')
+
+TIMEZONE_OFFSET
+
++TIMEZONE_OFFSET(timeZoneString, dateTerm|timeTerm|timestampTerm)
+
+
+ TIMEZONE_OFFSET ( timeZoneString , dateTerm 
timeTerm 
timestampTerm ) 
+
+
+
+Returns offset (shift in minutes) of a time zone at particular date/time in minutes.
+Example:
+TIMEZONE_OFFSET('Indian/Cocos', myDate)
+
 
 
-
-

Index

Functions

Aggregate Functions

FIRST_VALUE

LAST_VALUE

NTH_VALUE

STDDEV_POP

ANY

ALL

MD5

ENCODE

DECODE

COALESCE

LPAD

LENGTH

CONVERT_TZ

TIMEZONE_OFFSET