This page contains a list of all Oracle SQL functions. It contains SQL functions from Oracle 12c, as well as older versions such as 9i, 10g, and 11g.
It’s a great page for you to bookmark for future reference. You don’t need to search through Oracle documentation to remember how a certain function works!
The SQL functions have been grouped into sections. You can use your browser’s search feature (CTRL+F) to find a specific function very quickly – no need to wait for pages to load.
Each function contains:
- A short description of what the function does.
- The syntax of the function
- A link to a page that contains more information, such as examples, parameters, and common questions.
If you find this page helpful, please share it with your friends and coworkers or on websites you visit! Thanks!
If you have any questions or comments, please use the Comments section at the end of the page.
[text-blocks id=”5975″]
Aggregate Functions
AVG
Finds the average of all the values provided.
AVG ( [DISTINCT/ALL] expression ) OVER ( analytic_clause )
SQL AVG Function with Examples
COUNT
Finds the total number of items provided.
COUNT ( [ * | [ DISTINCT | ALL ] expression) [ OVER (analytic_clause) ]
SQL COUNT Function with Examples
MAX
Finds the highest value of all the values provided.
MAX ( [DISTINCT/ALL] expression ) [OVER (analytic_clause)]
SQL MAX Function With Examples
MIN
Finds the lowest value of all the values provided.
MIN ( [DISTINCT/ALL] expression ) [OVER ( analytic_clause )]
SQL MIN Function With Examples
SUM
Finds the sum total of all the values provided.
SUM ( [DISTINCT/ALL] expression ) [OVER (analytic_clause)]
SQL SUM Function With Examples
Conversion Functions
BIN_TO_NUM
Converts a bit vector to a number.
BIN_TO_NUM ( expression_list )
CAST
Converts one data type to another.
CAST( expression AS type_name )
CAST( MULTISET (subquery) AS type_name )
SQL CAST Function With Examples
COALESCE
Returns the first non-NULL value.
COALESCE( expr1, expr2, [expr…] )
SQL COALESCE Function With Examples
CHARTOROWID
Converts a string value to a rowid value.
CHARTOROWID ( input_char )
FROM_TZ
Converts the specified value to a TIMESTAMP WITH TIME ZONE type.
FROM_TZ ( timestamp_value, timezone_value )
Oracle Timezone Function Guide
HEXTORAW
Converts the specified hexadecimal string into a raw value.
HEXTORAW ( charvalue )ra
NUMTODSINTERVAL
Converts the specified number to an INTERVAL DAY TO SECOND value.
NUMTODSINTERVAL ( number, interval_unit )
NUMTOYMINTERVAL
Converts the specified number to an INTERVAL YEAR TO MONTH value.
NUMTOYMINTERVAL ( number, interval_unit )
RAWTOHEX
Converts a raw value to its hexadecimal character value.
RAWTOHEX ( charvalue )
RAWTONHEX
Converts a raw value to its hexadecimal character value in the national character set.
RAWTONHEX ( raw )
Oracle RAWTONHEX Function With Examples (coming soon)
ROWIDTOCHAR
Converts a ROWID value to a string
ROWIDTOCHAR ( rowid)
Oracle ROWIDTOCHAR Function With Examples (coming soon)
ROWIDTONCHAR
Converts a ROWID value to a string value in the national character set.
ROWIDTONCHAR ( rowid )
Oracle ROWIDTONCHAR Function With Examples (coming soon)
SCN_TO_TIMESTAMP
Converts a System Change Number into a timestamp.
SCN_TO_TIMESTAMP ( number )
Oracle SCN_TO_TIMESTAMP Function With Examples
TIMESTAMP_TO_SCN
Converts a timestamp into a System Change Number.
TIMESTAMP_TO_SCN ( timestamp )
Oracle TIMESTAMP_TO_SCN Function With Examples
TO_BINARY_DOUBLE
Converts a specified value into a single-precision floating point number.
TO_BINARY_DOUBLE ( expression [, format [, nlsparam ] ] )
Oracle TO_BINARY_DOUBLE Function With Examples (coming soon)
TO_CHAR
Converts a date or number to a string.
TO_CHAR( input_value, [format_mask], [nls_parameter] )
Oracle TO_CHAR Function With Examples
TO_CLOB
Converts a specified value into a CLOB value.
TO_CLOB ( input_string )
Oracle TO_CLOB Function With Examples
TO_DATE
Converts a string to a date value.
TO_DATE( charvalue, [format_mask], [nls_date_language] )
Oracle TO_DATE Function With Examples
TO_DSINTERVAL
Converts a string to an INTERVAL DAY TO SECOND type.
TO_DSINTERVAL ( input_string [, nlsparam] )
TO_LOB
Converts LONG values to LOB values.
TO_LOB ( long_value )
Oracle TO_LOB Function With Examples
TO_MULTI_BYTE
Converts the specified string into the same string with multi-byte characters.
TO_MULTI_BYTE ( string )
TO_NCHAR
Converts a string into a string of the national character set.
TO_NCHAR ( input_string )
TO_NCHAR ( input_datetime [, format [, nlsparam ] ] )
TO_NCHAR ( number [, format [, nlsparam ] ] )
Oracle TO_NCHAR Function With Examples (coming soon)
TO_NCLOB
Converts a specified value into a CLOB value of the national character set.
TO_NCLOB ( lob_value )
Oracle TO_NCLOB Function With Examples
TO_NUMBER
Converts a string value to a number.
TO_NUMBER( input_value, [format_mask], [nls_parameter] )
Oracle TO_NUMBER Function With Examples
TO_SINGLE_BYTE
Converts the specified string into the same string with single-byte characters.
TO_SINGLE_BYTE ( input_string )
TO_TIMESTAMP
Converts a string to a timestamp value.
TO_TIMESTAMP ( input_string, [format_mask], [‘nlsparam’] )
TO_TIMESTAMP_TZ
Converts a string to a TIMESTAMP WITH TIME ZONE type.
TO_TIMESTAMP_TZ ( input_string [, format_mask] [, nls_param] )
TO_YMINTERVAL
Converts a string to an INTERVAL YEAR TO MONTH type.
TO_YMINTERVAL ( input_string )
UNISTR
Converts a literal string into another value in the national character set.
UNISTR ( string )
Oracle UNISTR Function With Examples (coming soon)
Date and Time Functions
ADD_MONTHS
Returns a date value with a number of months added to it.
ADD_MONTHS ( input_date, number_months )
Oracle ADD_MONTHS Function With Examples
CURRENT_DATE
Returns the current date in the session time zone.
CURRENT_DATE
Oracle CURRENT_DATE Function With Examples
CURRENT_TIMESTAMP
Returns the current date and time in the session time zone.
CURRENT_TIMESTAMP ( [precision] )
Oracle CURRENT_TIMESTAMP Function With Examples
DBTIMEZONE
Returns the value of the database time zone.
DBTIMEZONE
Oracle DBTIMEZONE Function With Examples
LAST_DAY
Returns the date of the last day of the month containing the specified date.
LAST_DAY ( input_date )
Oracle LAST_DAY Function With Examples
LOCALTIMESTAMP
Returns the current date and time, in a TIMESTAMP value, using the session timezone.
LOCALTIMESTAMP ( timestamp_precision )
Oracle LOCALTIMESTAMP Function With Examples
MONTHS_BETWEEN
Finds the number of months between the two specified dates.
MONTHS_BETWEEN ( date1, date2 )
NEW_TIME
Returns the date and time in one timezone based on a second timezone.
NEW_TIME ( input_date, timezone1, timezone2 )
Oracle NEW_TIME Function With Examples
NEXT_DAY
Returns the first specified weekday after a specified date.
NEXT_DAY ( input_date, weekday )
Oracle NEXT_DAY Function With Examples
ORA_DST_AFFECTED
Determines if a TIMESTAMP WITH TIME ZONE will result in a nonexisting time or duplicate time error.
ORA_DST_AFFECTED ( datetime_expression )
Oracle ORA_DST_AFFECTED Function With Examples (coming soon)
ORA_DST_CONVERT
Specifies the error handling when changing the time zone data file for your database.
ORA_DST_CONVERT ( datetime_expression [, duplicate_time_handling [, nonexisting_time_handling ] ] )
Oracle ORA_DST_CONVERT Function With Examples (coming soon)
ORA_DST_ERROR
Determines if a TIMESTAMP WITH TIME ZONE will result in an error.
ORA_DST_ERROR ( datetime_expression )
Oracle ORA_DST_ERROR Function With Examples (coming soon)
SESSIONTIMEZONE
Returns the time zone of the current session.
SESSIONTIMEZONE
Oracle SESSIONTIMEZONE Function With Examples
SYS_EXTRACT_UTC
Extracts the UTC from a datetime value with a timezone offset.
SYS_EXTRACT_UTC ( datetime_with_timezone_value )
Oracle SYS_EXTRACT_UTC Function With Examples (coming soon)
SYSDATE
Returns the current date and time.
SYSDATE
Oracle SYSDATE Function With Examples (coming soon)
SYSTIMESTAMP
Returns the current date and time, including fractional seconds and timezone.
SYSTIMESTAMP
Oracle SYSTIMESTAMP Function With Examples
TZ_OFFSET
Returns the timezone offset from the value specified.
TZ_OFFSET ( timezone_name | time_value | SESSIONTIMEZONE | DBTIMEZONE )
Oracle TZ_OFFSET Function With Examples
Environment Functions
CON_DBID_TO_ID
Returns the container ID from a container DBID. Useful for multitenant container databases.
CON_DBID_TO_ID ( container_dbid )
Oracle CON_DBID_TO_ID Function With Examples (coming soon)
CON_GUID_TO_ID
Returns the container ID from a container GUID. Useful for multitenant container databases.
CON_GUID_TO_ID ( container_guid )
Oracle CON_GUID_TO_ID Function With Examples (coming soon)
CON_NAME_TO_ID
Returns the container ID from a container name. Useful for multitenant container databases.
CON_NAME_TO_ID ( container_name )
Oracle CON_NAME_TO_ID Function With Examples (coming soon)
CON_UID_TO_ID
Returns the container ID from a container UID. Useful for multitenant container databases.
CON_UID_TO_ID ( container_uid )
Oracle CON_UID_TO_ID Function With Examples (coming soon)
ORA_INVOKING_USER
Returns the name of the database user who invoked the current statement or view.
ORA_INVOKING_USER
Oracle ORA_INVOKING_USER Function With Examples (coming soon)
ORA_INVOKING_USERID
Returns the identifier of the database user who invoked the current statement or view.
ORA_INVOKING_USERID ( )
Oracle ORA_INVOKING_USERID Function With Examples (coming soon)
SYS_CONTEXT
Returns the parameter associated with the provided namespace.
SYS_CONTEXT (‘namespace’, ‘parameter’ [, length] )
Oracle SYS_CONTEXT Function With Examples
SYS_GUID
Generates and returns a GUID value.
SYS_GUID()
Oracle SYS_GUID Function With Examples (coming soon)
SYS_TYPEID
Returns the typeid of the type of the operand.
SYS_TYPEID ( object_type_value )
Oracle SYS_TYPEID Function With Examples (coming soon)
UID
Returns a value that uniquely identifies a user
UID
Oracle UID Function With Examples (coming soon)
USER
Returns the name of the user.
USER
Oracle SYS_CONTEXT Function with Examples (including Oracle 12C) – includes USER and USERENV
USERENV
Returns information about the current session.
USERENV (parameter)
Oracle SYS_CONTEXT Function with Examples (including Oracle 12C) – includes USER and USERENV
SQLCODE
Returns the error code of the most recent error.
SQLCODE
Oracle SQLCODE Function With Examples
SQLERRM
Returns the error message of the most recent error.
SQLERRM ( error_number )
Oracle SQLERRM Function With Examples
NLS Functions
NLS_CHARSET_DECL_LEN
Returns the declaration length of an NCHAR column.
NLS_CHARSET_DECL_LEN ( byte_count, char_set_id )
Oracle NLS_CHARSET_DECL_LEN Function With Examples (coming soon)
NLS_CHARSET_ID
Returns the character set ID number of the provided value.
NLS_CHARSET_ID ( string_value )
Oracle NLS_CHARSET_ID Function With Examples (coming soon)
NLS_CHARSET_NAME
Returns the character set name of the provided value.
NLS_CHARSET_NAME ( number )
Oracle NLS_CHARSET_NAME Function With Examples (coming soon)
Numeric and Maths Functions
ABS
Returns the absolute value of the provided number.
ABS( number )
Oracle ABS Function With Examples
ACOS
Finds the.
ACOS ( number)
Oracle ASIN ACOS ATAN ATAN2 Function With Examples
ASIN
Returns the arcsine of the provided number.
ASIN ( number)
Oracle ASIN ACOS ATAN ATAN2 Function With Examples
ATAN
Returns the arctangent of the provided number
ATAN ( number)
Oracle ASIN ACOS ATAN ATAN2 Function With Examples
ATAN2
Returns the arctangent of two provided numbers
ATAN2 ( number1 [/|,] number2 )
Oracle ASIN ACOS ATAN ATAN2 Function With Examples
BITAND
Returns an integer which represents the AND operation on the bits of the two provided values.
BITAND ( expr1, expr2 )
Oracle BITAND Function With Examples
CEIL
Returns the smallest whole number (integer) which is greater than or equal to the provided number.
CEIL ( input_val )
SQL CEIL Function With Examples
CORR
Returns the coefficient of correlation of a set of number pairs.
CORR ( expression1, expression2 )
Oracle CORR Function With Examples
CORR_K
Calculates Kendall’s tau-b correlation coefficient.
CORR_K ( expression1, expression2 [, COEFFICIENT | ONE_SIDED_SIG | ONE_SIDED_SIG_POS | ONE_SIDED_SIG_NEG | TWO_SIDED_SIG ] )
Oracle CORR_K Function With Examples (coming soon)
CORR_S
Calculates the Spearman’s rho correlation coefficient.
CORR_S ( expression1, expression2 [, COEFFICIENT | ONE_SIDED_SIG | ONE_SIDED_SIG_POS | ONE_SIDED_SIG_NEG | TWO_SIDED_SIG ] )
Oracle CORR_S Function With Examples (coming soon)
COS
Returns the cosine of the provided number.
COS ( number )
Oracle COS Function With Examples
COSH
Returns the hyperbolic cosine of the provided number.
COSH (number)
Oracle SINH COSH TANH Function With Examples
COVAR_POP
Returns the population covariance of a set of number pairs.
COVAR_POP ( expression1, expression2 )
COVAR_POP (expression1, expression2) OVER (analytic_clause)
Oracle COVAR_POP Function With Examples
COVAR_SAMP
Returns the sample covariance of a set of number pairs.
COVAR_SAMP (expression1, expression2)
COVAR_SAMP (expression1, expression2) OVER (analytic_clause)
Oracle COVAR_SAMP Function With Examples
CUME_DIST
Returns the cumulative distribution of a value in a group of values.
CUME_DIST (expression1, … expression_n) WITHIN GROUP (ORDER BY expression_order1, … expression_order_n)
CUME_DIST() OVER ( [query_partition_clause] ORDER BY order_clause )
Oracle CUME_DIST Function With Examples
DENSE_RANK
Returns the rank of a row in an ordered group of rows.
DENSE_RANK ( expr, [expr(n)] ) WITHIN GROUP ( ORDER BY (order_expr [ASC|DESC] [NULLS FIRST|LAST] )
DENSE_RANK() OVER ( [query_partition_clause] order_by_clause)
SQL RANK and DENSE_RANK Function With Examples
EXP
Returns the standard value of e raised to the power of the provided number.
EXP ( number )
Oracle EXP Function With Examples
EXTRACT
Extracts and returns a field from within a specified datetime value.
EXTRACT ( date_component FROM expression )
Oracle EXTRACT Function With Examples
FLOOR
Returns the largest whole number (integer) which is less than or equal to the provided number.
FLOOR ( input_number )
SQL FLOOR Function With Examples
GREATEST
Returns the greatest value from the list of provided values.
GREATEST ( expr1, [expr_n] )
SQL GREATEST and LEAST Function With Examples
LEAST
Returns the least value from the list of provided values.
LEAST ( expr1, [expr_n] )
SQL GREATEST and LEAST Function With Examples
LN
Returns the natural logarithm of the provided number.
LN ( number )
Oracle LN Function With Examples
LOG
Returns the logarithm with a base of the first provided value, of the second provided value.
LOG ( [base, ] expression )
MEDIAN
Finds the median value of all the values provided.
MEDIAN ( expr ) OVER (query_partition_clause)
Oracle MEDIAN Function With Examples
MOD
Returns the remainder of the first provided value divided by the second provided value.
MOD ( numerator, denominator )
Oracle MOD Function With Examples
ORA_HASH
Calculates a hash value for an expression.
ORA_HASH ( expression [, max_bucket [, seed_value ] ] )
Oracle ORA_HASH Function With Examples (coming soon)
PERCENT_RANK
Determines the rank of a row between 0 and 1.
PERCENT_RANK ( expression ) WITHIN GROUP ( ORDER BY (expression_n [. DESC | ASC ] [NULLS FIRST|LAST] )
PERCENT_RANK () OVER ( [query_partition_clause] order_by_clause )
Oracle PERCENT_RANK Function With Examples (coming soon)
PERCENTILE_CONT
Determines a percentile using a continuous distribution model.
PERCENTILE_CONT ( expression) WITHIN GROUP ( ORDER BY expression [ ASC | DESC ] [OVER ( query_partition_clause )
Oracle PERCENTILE_CONT Function With Examples (coming soon)
PERCENTILE_DISC
Determines a percentile using a discrete distribution model.
PERCENTILE_DISC ( expression) WITHIN GROUP ( ORDER BY expression [ ASC | DESC ] [OVER ( query_partition_clause )
Oracle PERCENTILE_DISC Function With Examples (coming soon)
POWER
Returns the first provided number raised to the power of the second provided number.
POWER ( n2, n1 )
SQL POWER and SQRT Function With Examples
RANK
Calculates the rank of a value in a group of values.
RANK ( expr ) WITHIN GROUP ( ORDER BY ( order_expr [NULLS FIRST/LAST] ) )
RANK () OVER ( [query_partition_clause] order_by_clause )
SQL RANK and DENSE_RANK Function With Examples
REMAINDER
Returns the remainder of the first provided number divided by the second provided number.
REMAINDER ( n2, n1 )
Oracle REMAINDER Function With Examples
ROUND
Returns a number which is the provided value rounded to a specific number of decimal places, or returns a DATE value to the specified format.
ROUND ( input, roundto )
SQL ROUND Function With Examples
ROWNUM
A pseudocolumn that displays the number of the record in the result set, before ordering.
ROWNUM
Oracle ROWNUM vs ROWNUMBER (coming soon)
ROW_NUMBER
Returns a number for each row, using a specified partition and ordering.
ROW_NUMBER () OVER ( [ query_partition_clause] order_by_clause )
Oracle ROWNUM vs ROWNUMBER (coming soon)
SIGN
Returns the sign of the provided value.
SIGN ( number )
Oracle SIGN Function With Examples
SIN
Returns the sine of the provided value.
SIN ( number )
Oracle SIN Function With Examples
SINH
Returns the hyperbolic sine of the provided value.
SINH ( number )
Oracle SINH, COSH, and TANH Functions With Examples
SQRT
Returns the square root of the provided number
SQRT ( number )
SQL POWER and SQRT Function With Examples
STANDARD_HASH
Calculates a hash value for an expression using one of several hash algorithms.
STANDARD_HASH ( expression [, method ] )
Oracle STANDARD_HASH Function With Examples (coming soon)
STDDEV
Returns the standard deviation of a set of numbers.
STDDEV ( [DISTINCT | ALL] expression )
STDDEV ( [DISTINCT | ALL] expression ) [OVER (analytical_clause) ]
Oracle STDDEV Function With Examples
STDDEV_POP
Calculates the population standard deviation and returns the square root of the population variance.
STDDEV_POP ( expression) [ OVER ( analytic_clause ) ]
Oracle STDDEV_POP Function With Examples (coming soon)
STDDEV_SAMP
Calculates the cumulative sample standard deviation and returns the square root of the sample variance.
STDDEV_SAMP ( expression) [ OVER ( analytic_clause ) ]
Oracle STDDEV_SAMP Function With Examples (coming soon)
TAN
Returns the tangent of the provided number.
TAN ( number )
Oracle TAN Function With Examples
TANH
Returns the hyperbolic tangent of the provided number.
TANH ( number )
Oracle SINH, COSH, and TANH Functions With Examples
TRUNC
Truncates the provided number to specified decimal places, or returns the date portion of a datetime value.
TRUNC ( date, fmt )
TRUNC ( number, decimals )
Oracle TRUNC Function With Examples
VAR_POP
Returns the population variance of a set of numbers after discarding nulls.
VAR_POP ( expression) [ OVER ( analytic_clause ) ]
Oracle VAR_POP Function With Examples (coming soon)
VAR_SAMP
Returns the sample variance of a set of numbers after discarding nulls.
VAR_SAMP ( expression) [ OVER ( analytic_clause ) ]
Oracle VAR_SAMP Function With Examples (coming soon)
VARIANCE
Returns the variance of the specified expression.
VARIANCE ( [ DISTINCT | ALL ] expression) [ OVER ( analytic_clause ) ]
Oracle VARIANCE Function With Examples (coming soon)
WIDTH_BUCKET
Constructs histograms based on provided values.
WIDTH_BUCKET ( expression, min_value, max_value, num_buckets )
Oracle WIDTH_BUCKET Function With Examples (coming soon)
String and Character Functions
ASCII
Returns the number code that represents the provided character.
ASCII ( charvalue )
Oracle ASCII Function With Examples
ASCIISTR
Converts a string in any character set to an ASCII string using the database character set.
ASCIISTR ( charvalue )
Oracle ASCIISTR Function With Examples
CHR
Returns a character which has a binary value of the number provided.
CHR ( number_code [USING NCHAR_CS] )
Oracle CHR Function With Examples
COMPOSE
Converts the provided value into a Unicode string.
COMPOSE ( input_value )
CONCAT
Returns a value where the provided strings have been concatenated.
CONCAT( string1, string2 )
Oracle CONCAT Function With Examples
CONVERT
Converts a string from one character set to another.
CONVERT ( input_char, dest_char_set, [source_char_set] )
Oracle CONVERT Function With Examples
DECODE
Looks at one value, and returns different values depending on what it is checked against. Similar to CASE.
DECODE ( expression, search, result [, search, result]… [,default] )
Oracle DECODE Function With Examples
DECOMPOSE
Converts the provided value into a string value with separated Unicode characters.
DECOMPOSE ( input_string [CANONICAL|COMPATIBILITY]
DUMP
Returns a value containing the data type, length, and internal representation of a value.
DUMP ( expression [, return_format] [, start_position] [, length] )
Oracle DUMP Function With Examples
INITCAP
Returns a value where the first letter in each word is uppercase, and all others are lowercase.
INITCAP ( input_string )
SQL UPPER, LOWER, and INITCAP Functions
INSTR
Searches for a value within another value.
INSTR( string, substring, [start_position], [occurrence] )
SQL INSTR Function With Examples
INSTR2
Searches for a value within another value, but calculates length using USC2 code points.
INSTR2 ( string, substring, [start_position], [occurrence] )
Oracle INSTRB, INSTR, INSTR2, and INSTR4 Functions With Examples
INSTR4
Searches for a value within another value, but calculates length using USC4 code points.
INSTR4 ( string, substring, [start_position], [occurrence] )
Oracle INSTRB, INSTR, INSTR2, and INSTR4 Functions With Examples
INSTRB
Searches for a value within another value, but calculates length using bytes
INSTRB ( string, substring, [start_position], [occurrence] )
Oracle INSTRB, INSTR, INSTR2, and INSTR4 Functions With Examples
INSTRC
Searches for a value within another value, but calculates length using Unicode characters.
INSTRC ( string, substring, [start_position], [occurrence] )
Oracle INSTRB, INSTR, INSTR2, and INSTR4 Functions With Examples
LISTAGG
Allows you to aggregate strings from data in columns.
LISTAGG( measure_expr [, delimiter]) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]
Oracle LISTAGG Function With Examples
LENGTH
Returns the length of the provided string.
LENGTH ( string_value )
Oracle LENGTH Function With Examples
LENGTH2
Returns the length of the provided string in USC2 code points.
LENGTH2 ( string_value )
Oracle LENGTH2, LENGTH4, LENGTHB, and LENGTHC Function with Examples
LENGTH4
Returns the length of the provided string in USC4 code points.
LENGTH4 ( string_value )
Oracle LENGTH2, LENGTH4, LENGTHB, and LENGTHC Function with Examples
LENGTHB
Returns the length of the provided string in bytes.
LENGTHB ( string_value )
Oracle LENGTH2, LENGTH4, LENGTHB, and LENGTHC Function with Examples
LENGTHC
Returns the length of the provided string in Unicode characters.
LENGTHC ( string_value )
Oracle LENGTH2, LENGTH4, LENGTHB, and LENGTHC Function with Examples
LOWER
Returns a value where all characters in the provided string are in lowercase.
LOWER ( input_string )
SQL UPPER, LOWER, and INITCAP Functions
LPAD
Adds the specified number and type of characters to the left of the specified value.
LPAD( expr, length [, pad_expr] )
SQL LPAD Function With Examples
LTRIM
Removes the specified characters from the left of the provided value.
LTRIM( input_string, [trim_string] )
Oracle LTRIM Function With Examples
LNNVL
Calculates if one or both operands of a condition are null.
LNNVL ( condition )
Oracle LNNVL Function With Examples (coming soon)
NCHR
Returns a character which has a binary value of the number provided, in the national character set.
NCHR ( number_code )
Oracle NCHR Function With Examples
NLS_INITCAP
Returns a value where the first letter in each word is uppercase, and all others are lowercase, but a sort order can be specified.
NLS_INITCAP ( input_char [, nlsparam ] )
Oracle NLS_INITCAP Function With Examples (coming soon)
NLS_LOWER
Returns a value where the provided value is in lowercase, but a sort order can be specified.
NLS_LOWER ( input_char [, nlsparam ] )
Oracle NLS_LOWER Function With Examples (coming soon)
NLS_UPPER
Returns a value where the provided value is in uppercase, but a sort order can be specified.
NLS_UPPER ( input_char [, nlsparam ] )
Oracle NLS_UPPER Function With Examples (coming soon)
NLSSORT
Returns a string of bytes that can be used to sort the provided value.
NLSSORT ( input_char [, nlsparam ] )
Oracle NLSSORT Function With Examples (coming soon)
NANVL
Returns an alternative value if one value is not a number.
NANVL ( check_value, replace_value )
Oracle NANVL Function With Examples (coming soon)
NVL
Returns a different value if the first value is NULL.
NVL( check_value, replace_value )
Oracle NVL Function With Examples
NVL2
Returns a different value if a NULL value is supplied.
NVL2( value_to_check, value_if_not_null, value_if_null )
Oracle NVL2 Function With Examples
NULLIF
Returns a different value if a NULL value is supplied.
NULLIF (expr1, expr2)
SQL NULLIF Function With Examples
REGEXP_COUNT
Counts the number of times a pattern appears in a string, using regular expressions.
REGEXP_COUNT ( source_char, pattern [, position [, match_pattern [, subexpression ] ] ] )
Oracle REGEXP_COUNT Function With Examples (coming soon)
REGEXP_INSTR
Searches for a value within another value, using regular expressions.
REGEXP_INSTR ( source_char, pattern [, position [, occurrence [, return_option [, match_pattern [, subexpression ] ] ] ] ] )
Oracle REGEXP_INSTR Function With Examples (coming soon)
REGEXP_REPLACE
Allows characters to be replaced using regular expressions.
REGEXP_REPLACE ( source_char, pattern [, replace_string [, position [, occurrence [, match_parameter ] ] ] ] )
Oracle REGEXP_REPLACE Function With Examples (coming soon)
REGEXP_SUBSTR
Allows characters to be searched using regular expressions.
REGEXP_SUBSTR ( source_char, pattern [, position [, occurrence [, match_parameter ] ] ] ] )
Oracle REGEXP_SUBSTR Function With Examples (coming soon)
REPLACE
Replaces one string with another string in the provided value.
REPLACE ( whole_string, string_to_replace, [replacement_string])
Oracle REPLACE Function With Examples
RPAD
Adds the specified number and type of characters to the right of the specified value.
RPAD( expr, length [, pad_expr] )
SQL RPAD Function With Examples
RTRIM
Removes the specified characters from the right of the provided value.
RTRIM ( input_string, [trim_character])
Oracle RTRIM Function With Examples
SOUNDEX
Returns a string containing a phonetic representation of the provided value.
SOUNDEX (string )
Oracle SOUNDEX Function With Examples
SUBSTR
Returns a part of the provided value, based on the provided position and length.
SUBSTR (string, start_position, [length] )
Oracle SUBSTR Function With Examples
TRANSLATE
Replaces one string with the equivalent string in the provided value.
TRANSLATE ( source_string, from_string, to_string )
Oracle TRANSLATE Function With Examples
TRANSLATE USING
Converts a string into a character set, either CHAR_CS or NCHAR_CS.
TRANSLATE ( charvalue USING {CHAR_CS|NCHAR_CS} )
Oracle TRANSLATE USING Function With Examples (coming soon)
TREAT
Changes the declared type of an expression.
TREAT ( expression AS [ REF ] [ schema. ] type )
Oracle TREAT Function With Examples (coming soon)
TRIM
Removes the leading and trailing characters from a string.
TRIM ( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] trim_source )
Oracle TRIM Function With Examples
UPPER
Returns a value where all characters in the provided string are in uppercase.
UPPER ( input_string )
SQL UPPER, LOWER, and INITCAP Functions
VSIZE
Returns the number of bytes an expression represents.
VSIZE ( expression )
Oracle LENGTH and VSIZE Functions Guide, FAQ, and Examples
Analytic Functions
FIRST_VALUE
Returns the first value in an ordered set of values.
FIRST_VALUE ( expression [ IGNORE NULLS ] ) OVER ( analytic_clause )
Oracle FIRST_VALUE Function With Examples (coming soon)
LAST_VALUE
Returns the last value in an ordered set of values.
LAST_VALUE ( expression [ IGNORE NULLS ] ) OVER ( analytic_clause )
Oracle LAST_VALUE Function With Examples (coming soon)
LAG
Returns a value from a row further up in your result set.
LAG ( expression [, offset [, default] ] ) OVER ( [ query_partition_clause ] order_by_clause )
SQL LAG Function With Examples
LEAD
Returns a value from a row further down in your result set.
LEAD ( expression [, offset [, default] ] ) OVER ( [ query_partition_clause ] order_by_clause )
SQL LEAD Function With Examples
NTILE
Divides an ordered data set into a number of buckets and assigns a bucket number to each row.
NTILE ( expression ) OVER ( [query_partition_clause] order_by_clause )
Oracle NTILE Function With Examples (coming soon)
RATIO_TO_REPORT
Calculates the ratio of a value to the sum of a set of values.
RATIO_TO_REPORT( expression ) OVER ( [query_partition_clause] )
Oracle RATIO_TO_REPORT Function With Examples (coming soon)
Linear Regression Functions
REGR_AVGX
Returns the average of the expression1 of the regression line.
REGR_AVGX ( expression1, expression2) [ OVER ( analytic_clause ) ]
Oracle REGR_AVGX Function With Examples (coming soon)
REGR_AVGY
Returns the average of the expression2 of the regression line.
REGR_AVGY ( expression1, expression2) [ OVER ( analytic_clause ) ]
Oracle REGR_AVGY Function With Examples (coming soon)
REGR_COUNT
Returns the number of non-null number pairs in the regression line.
REGR_COUNT ( expression1, expression2) [ OVER ( analytic_clause ) ]
Oracle REGR_COUNT Function With Examples (coming soon)
REGR_INTERCEPT
Returns the Y-intercept of the regression line.
REGR_INTERCEPT ( expression1, expression2) [ OVER ( analytic_clause ) ]
Oracle REGR_INTERCEPT Function With Examples (coming soon)
REGR_R2
Returns the coefficient of determination for the regression.
REGR_R2 ( expression1, expression2) [ OVER ( analytic_clause ) ]
Oracle REGR_R2 Function With Examples (coming soon)
REGR_SLOPE
Calculates the slope of a linear regression line.
REGR_SLOPE ( expression1, expression2) [ OVER ( analytic_clause ) ]
Oracle REGR_SLOPE Function With Examples (coming soon)
REGR_SXX
Returns the REGR_COUNT function multiplied by VAR_POP(expression2)
REGR_SXX ( expression1, expression2) [ OVER ( analytic_clause ) ]
Oracle REGR_SXX Function With Examples (coming soon)
REGR_SXY
Returns the REGR_COUNT function multiplied by COVAR_POP(expression2, expression2)
REGR_SXY ( expression1, expression2) [ OVER ( analytic_clause ) ]
Oracle REGR_SXY Function With Examples (coming soon)
REGR_SYY
Returns the REGR_COUNT function multiplied by VAR_POP(expression1)
REGR_SYY ( expression1, expression2) [ OVER ( analytic_clause ) ]
Oracle REGR_SYY Function With Examples (coming soon)
Other Statements
CASE
Allows the usage of IF-THEN-ELSE logic in an SQL statement.
CASE [expression] WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
…
WHEN condition_n THEN result_n
ELSE result
END case_name
SQL CASE Statement With Examples
SYS_CONNECT_BY_PATH
A hierarchical function that returns the path of a column value from root to node.
SYS_CONNECT_BY_PATH ( column, character_separator )
Oracle SYS_CONNECT_BY_PATH Function With Examples (coming soon)
Grouping Functions
GROUP_ID
Returns a number to uniquely identify groups and to help identify duplicate groups.
GROUP_ID ()
Oracle GROUP_ID Function is discussed further in the guide to GROUP BY.
GROUPING
Identifies if a row is a super-aggregate row or a regular grouped row (such as rows that have a NULL value).
GROUPING ( expression)
Oracle GROUPING Function With Examples (coming soon)
GROUPING_ID
Returns a number to determine if a column is a grouped value or not.
GROUPING_ID ( expression1 [, expression_n ] )
Oracle GROUPING_ID Function With Examples (coming soon)
Large Object Functions
BFILENAME
Returns a BFILE for a LOB binary file in the file system.
BFILENAME ( directory, filename )
Oracle BFILENAME Function With Examples
EMPTY_BLOB
Returns an empty BLOB locator.
EMPTY_BLOB ()
Oracle EMPTY_BLOB Function With Examples
EMPTY_CLOB
Returns an empty CLOB locator.
EMPTY_CLOB ()
Oracle EMPTY_CLOB Function With Examples
Statistical Functions
STATS_BINOMIAL_TEST
Tests the difference between a sample proportion and a given proportion.
STATS_BINOMIAL_TEST ( expression1, expression2, p [, TWO_SIDED_PROB | EXACT_PROB | ONE_SIDED_PROB_OR_MORE | ONE_SIDED_PROB_OR_LESS ] )
Oracle STATS_BINOMIAL_TEST Function With Examples (coming soon)
STATS_CROSSTAB
Analyses two nominal variables and returns a specified statistic.
STATS_CROSSTAB ( expression1, expression2 [, CHISQ_OBS | CHISQ_SIG | CHISQ_DIF | PHI_COEFFICIENT | CRAMERS_V | CONT_COEFFICIENT | COHENS_K ] )
Oracle STATS_CROSSTAB Function With Examples (coming soon)
STATS_F_TEST
Tests whether two variances are significantly different.
STATS_F_TEST ( expression1, expression2 [, TWO_SIDED_SIG | STATISTIC expression3 | DF_NUM expression3 | DF_DEN expression3 | ONE_SIDED_SIG expression3 ] )
Oracle STATS_F_TEST Function With Examples (coming soon)
STATS_KS_TEST
Compares two samples using a Kolmogorov-Smirnov function.
STATS_KS_TEST( expression1, expression [, STATISTIC | SIG ] )
Oracle STATS_KS_TEST Function With Examples (coming soon)
STATS_MODE
Finds the value that occurs with the greatest frequency from a set of values.
STATS_MODE ( expression)
Oracle STATS_MODE Function With Examples (coming soon)
STATS_MW_TEST
Tests two samples against a hypothesis using the Mann Whitney test.
STATS_MW_TEST ( expression1, expression2 [, STATISTIC | U_STATISTIC | ONE_SIDED_SIG expression3 | TWO_SIDED_SIG ] )
Oracle STATS_MW_TEST Function With Examples (coming soon)
STATS_ONE_WAY_ANOVA
Performs a one-way analysis of variance function.
STATS_ONE_WAY_ANOVA ( expression1, expression2 [, SUM_SQUARES_BETWEEN | SUM_SQUARES_WITHIN | DF_BETWEEN | DF_WITHIN | MEAN_SQUARES_BETWEEN | MEAN_SQUARES_WITHIN | F_RATIO | SIG ] )
Oracle STATS_ONE_WAY_ANOVA Function With Examples (coming soon)
STATS_T_TEST_INDEP
Performs a t-test of two independent groups with the same variance.
STATS_T_TEST_INDEP ( expression1, expression2 [, STATISTIC expression3 | ONE_SIDED_SIG expression3 | TWO_SIDED_SIG | DF ] )
Oracle STATS_T_TEST_INDEP Function With Examples (coming soon)
STATS_T_TEST_INDEPU
Performs a t-test of two independent groups with different variance.
STATS_T_TEST_INDEPU ( expression1, expression2 [, STATISTIC expression3 | ONE_SIDED_SIG expression3 | TWO_SIDED_SIG | DF ] )
Oracle STATS_T_TEST_INDEPU Function With Examples (coming soon)
STATS_T_TEST_ONE
Performs a one-sample t-test.
STATS_T_TEST_ONE ( expression1, expression2 [, STATISTIC expression3 | ONE_SIDED_SIG expression3 | TWO_SIDED_SIG | DF ] )
Oracle STATS_T_TEST_ONE Function With Examples (coming soon)
STATS_T_TEST_PAIRED
Performs a two-sample, paired t-test.
STATS_T_TEST_PAIRED ( expression1, expression2 [, STATISTIC expression3 | ONE_SIDED_SIG expression3 | TWO_SIDED_SIG | DF ] )
Oracle STATS_T_TEST_PAIRED Function With Examples (coming soon)
STATS_WSR_TEST
Performs a Wilcoxon Signed Ranks test to see if the median of the differences between the samples is significantly different from zero.
STATS_WSR_TEST ( expression1, expression2 [, STATISTIC | ONE_SIDED_SIG | TWO_SIDED_SIG ] )
Oracle STATS_WSR_TEST Function With Examples (coming soon)