Oracle Sql Function Cheat Sheet
ADVERTISEMENT
1Oracle SQL Function Cheat Sheet
Aggregate Functions
INITCAP ( input_string )
Numeric and Maths Functions
INSTR ( string, substring, [start_position],
AVG ( [DISTINCT|ALL] expression ) [OVER (
[occurrence] )
ABS ( number )
analytic_clause )]
INSTR2 ( string, substring, [start_position],
ACOS ( number )
COUNT ( [ * | [ DISTINCT | ALL ] expression) [OVER
[occurrence] )
ASIN ( number )
(analytic_clause)]
INSTR4 ( string, substring, [start_position],
ATAN2 ( number1 [/|,] number2 )
MAX ( [DISTINCT|ALL] expression ) [OVER
[occurrence] )
BITAND ( expr1, expr2 )
(analytic_clause)]
INSTRB ( string, substring, [start_position],
CEIL ( input_val )
MIN ( [DISTINCT|ALL] expression ) [OVER (
[occurrence] )
CORR ( expression1, expression2 )
analytic_clause )]
INSTRC ( string, substring, [start_position],
COS ( number )
SUM ( [DISTINCT|ALL] expression ) [OVER
[occurrence] )
COSH (number)
(analytic_clause)]
LISTAGG ( measure_expr [, delimiter]) WITHIN
COVAR_POP ( expression1, expression2 ) [OVER
GROUP (order_by_clause) [OVER
Conversion Functions
(analytic_clause)]
query_partition_clause]
COVAR_SAMP ( expression1, expression2 ) [OVER
BIN_TO_NUM ( expression_list )
LENGTH ( string_value )
(analytic_clause)]
CAST ( expression AS type_name )
LENGTH2 ( string_value )
CUME_DIST (expression1, … expression_n) WITHIN
CAST ( MULTISET (subquery) AS type_name )
LENGTH4 ( string_value )
GROUP (ORDER BY expression_order1, …
COALESCE ( expr1, expr2, [expr…] )
LENGTHB ( string_value )
expression_order_n)
CHARTOROWID ( input_char )
LENGTHC ( string_value )
CUME_DIST() OVER ( [query_partition_clause]
FROM_TZ ( timestamp_value, timezone_value )
LOWER ( input_string )
ORDER BY order_clause )
HEXTORAW ( charvalue )
LPAD( expr, length [, pad_expr] )
DENSE_RANK ( expr, [expr(n)] ) WITHIN GROUP (
NUMTODSINTERVAL ( number, interval_unit )
LTRIM( input_string, [trim_string] )
ORDER BY (order_expr [ASC|DESC] [NULLS
NUMTOYMINTERVAL ( number, interval_unit )
LNNVL ( condition )
FIRST|LAST] )
RAWTOHEX ( charvalue )
NCHR ( number_code )
DENSE_RANK() OVER ( [query_partition_clause]
RAWTONHEX ( raw )
NLS_INITCAP ( input_char [, nlsparam ] )
order_by_clause)
ROWIDTOCHAR ( rowid )
NLS_LOWER ( input_char [, nlsparam ] )
EXP ( number )
ROWIDTONCHAR ( rowid )
NLS_UPPER ( input_char [, nlsparam ] )
EXTRACT ( date_component FROM expression )
SCN_TO_TIMESTAMP ( number )
NLSSORT ( input_char [, nlsparam ] )
FLOOR ( input_number )
TIMESTAMP_TO_SCN ( timestamp )
NANVL ( check_value, replace_value )
GREATEST ( expr1, [expr_n] )
TO_BINARY_DOUBLE ( expression [, format [,
NVL ( check_value, replace_value )
LEAST ( expr1, [expr_n] )
nlsparam ] ] )
NVL2 ( value_to_check, value_if_not_null,
LN ( number )
TO_CHAR( input_value, [format_mask],
value_if_null )
LOG ( [base, ] expression )
[nls_parameter] )
NULLIF ( expr1, expr2 )
MEDIAN ( expr ) [OVER (query_partition_clause)]
TO_CLOB ( input_string )
REGEXP_COUNT ( source_char, pattern [, position [,
MOD ( numerator, denominator )
TO_DATE( charvalue, [format_mask],
match_pattern [, subexpression ] ] ] )
ORA_HASH ( expression [, max_bucket [,
[nls_date_language] )
REGEXP_INSTR ( source_char, pattern [, position [,
seed_value ] ] )
TO_DSINTERVAL ( input_string [, nlsparam] )
occurrence [, return_option [, match_pattern [,
PERCENT_RANK ( expression ) WITHIN GROUP (
TO_LOB ( long_value )
subexpression ] ] ] ] ] )
ORDER BY (expression_n [. DESC | ASC ] [NULLS
TO_MULTI_BYTE ( string )
REGEXP_REPLACE ( source_char, pattern [,
FIRST|LAST] )
TO_NCHAR ( input_string )
replace_string [, position [, occurrence [,
PERCENT_RANK () OVER ( [query_partition_clause]
TO_NCHAR ( input_datetime [, format [,nlsparam ] ] )
match_parameter ] ] ] ] )
order_by_clause )
TO_NCHAR ( number [, format [, nlsparam ] ] )
REGEXP_SUBSTR ( source_char, pattern [, position
PERCENTILE_CONT ( expression) WITHIN GROUP
TO_NCLOB ( lob_value )
[, occurrence [, match_parameter ] ] ] ] )
( ORDER BY expression [ ASC | DESC ] [OVER (
TO_NUMBER ( input_value, [format_mask],
REPLACE ( whole_string, string_to_replace,
query_partition_clause )
[nls_parameter] )
[replacement_string])
PERCENTILE_DISC ( expression) WITHIN GROUP (
TO_SINGLE_BYTE ( input_string )
RPAD ( expr, length [, pad_expr] )
ORDER BY expression [ ASC | DESC ] [OVER (
TO_TIMESTAMP ( input_string, [format_mask],
RTRIM ( input_string, [trim_character])
query_partition_clause )
[‘nlsparam’] )
SOUNDEX ( string )
POWER ( n2, n1 )
TO_TIMESTAMP_TZ ( input_string [, format_mask] [,
SUBSTR ( string, start_position, [length] )
RANK ( expr ) WITHIN GROUP ( ORDER BY (
nls_param] )
TRANSLATE ( source, from_string, to_string )
order_expr [NULLS FIRST/LAST] ) )
TO_YMINTERVAL ( input_string )
TRANSLATE ( charvalue USING
RANK () OVER ( [query_partition_clause]
UNISTR ( string )
{CHAR_CS|NCHAR_CS} )
order_by_clause )
TREAT ( expression AS [ REF ] [ schema. ] type )
REMAINDER ( n2, n1 )
Date and Time Functions
TRIM ( [ [ LEADING | TRAILING | BOTH ]
ROUND ( input, roundto )
trim_character FROM ] trim_source )
ROWNUM
ADD_MONTHS ( input_date, number_months )
UPPER ( input_string )
ROW_NUMBER () OVER ( [ query_partition_clause]
CURRENT_DATE
VSIZE ( expression )
order_by_clause )
CURRENT_TIMESTAMP ( [precision] )
SIGN ( number )
Analytic Functions
DBTIMEZONE
SIN ( number )
LAST_DAY ( input_date )
SINH ( number )
FIRST_VALUE ( expression [ IGNORE NULLS ] )
LOCALTIMESTAMP ( timestamp_precision )
SQRT ( number )
OVER ( analytic_clause )
MONTHS_BETWEEN ( date1, date2 )
STANDARD_HASH ( expression [, method ] )
LAST_VALUE ( expression [ IGNORE NULLS ] )
NEW_TIME ( input_date, timezone1, timezone2 )
STDDEV ( [DISTINCT | ALL] expression ) [OVER
OVER ( analytic_clause )
NEXT_DAY ( input_date, weekday )
(analytical_clause) ]
LAG ( expression [, offset [, default] ] ) OVER ( [
SESSIONTIMEZONE
STDDEV_POP ( expression) [ OVER (
query_partition_clause ] order_by_clause )
SYS_EXTRACT_UTC (
analytic_clause ) ]
LEAD ( expression [, offset [, default] ] ) OVER ( [
datetime_with_timezone_value )
STDDEV_SAMP ( expression) [ OVER (
query_partition_clause ] order_by_clause )
SYSDATE
analytic_clause ) ]
NTILE ( expression ) OVER (
SYSTIMESTAMP
TAN ( number )
[query_partition_clause] order_by_clause )
TZ_OFFSET ( timezone_name | time_value |
TANH ( number )
RATIO_TO_REPORT( expression ) OVER (
SESSIONTIMEZONE | DBTIMEZONE )
TRUNC ( date, fmt )
[query_partition_clause] )
TRUNC ( number, decimals )
Environment Functions
Other Functions
VAR_POP ( expression) [OVER ( analytic_clause )]
CON_DBID_TO_ID ( container_dbid )
VAR_SAMP ( expression) [OVER ( analytic_clause )]
CASE [expression]
CON_GUID_TO_ID ( container_guid )
VARIANCE ( [ DISTINCT | ALL ] expression) [ OVER
WHEN condition_1 THEN result_1
CON_NAME_TO_ID ( container_name )
( analytic_clause ) ]
WHEN condition_n THEN result_n
CON_UID_TO_ID ( container_uid )
WIDTH_BUCKET ( expression, min_value,
ELSE result
ORA_INVOKING_USER
max_value, num_buckets )
END case_name
ORA_INVOKING_USERID ( )
String and Character Functions
SYS_CONTEXT (namespace, parameter [, length] )
SYS_CONNECT_BY_PATH ( column,
SYS_GUID()
ASCII ( charvalue )
character_separator )
SYS_TYPEID ( object_type_value )
ASCIISTR ( charvalue )
UID
CHR ( number_code [USING NCHAR_CS] )
USER
Grouping Functions
COMPOSE ( input_value )
USERENV ( parameter )
CONCAT( string1, string2 )
GROUP_ID ( )
SQLCODE
CONVERT ( input_char, dest_char_set,
GROUPING ( expression )
SQLERRM ( error_number )
[source_char_set] )
GROUPING_ID ( expression1 [, expression_n ] )
NLS Functions
DECODE ( expression, search, result [, search,
result]… [,default] )
Large Object Functions
NLS_CHARSET_DECL_LEN ( byte_count,
DECOMPOSE ( input_string
BFILENAME ( directory, filename )
char_set_id )
[CANONICAL|COMPATIBILITY] )
EMPTY_BLOB ()
NLS_CHARSET_ID ( string_value )
DUMP ( expression [, return_format] [, start_position]
EMPTY_CLOB ()
NLS_CHARSET_NAME ( number )
[, length] )
ADVERTISEMENT
0 votes
Related Articles
Related forms
Related Categories
Parent category: Education