FB

Oracle SQL Functions – The Complete List

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 )

Oracle Interval Functions

NUMTOYMINTERVAL

Converts the specified number to an INTERVAL YEAR TO MONTH value.

NUMTOYMINTERVAL ( number, interval_unit )

Oracle Interval Functions

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] )

Oracle Interval Functions

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’] )

Oracle Timezone Functions

TO_TIMESTAMP_TZ

Converts a string to a TIMESTAMP WITH TIME ZONE type.

TO_TIMESTAMP_TZ ( input_string [, format_mask] [, nls_param] )

Oracle Timezone Functions

TO_YMINTERVAL

Converts a string to an INTERVAL YEAR TO MONTH type.

TO_YMINTERVAL ( input_string )

Oracle Interval Functions

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 )

Oracle Date Functions

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)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.