Skip Headers
Oracle® Database SQL Quick Reference
10g Release 2 (10.2)

Part Number B14195-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

2 SQL Functions

This chapter presents the syntax for SQL functions.

This chapter includes the following section:

Syntax for SQL Functions

A function is a command that manipulates data items and returns a single value.

The sections that follow show each SQL function and its related syntax. Refer to Chapter 5, "Subclauses" for the syntax of the subclauses.

See Also:

Functions in Oracle Database SQL Reference for detailed information about SQL functions

ABS

ABS(n)

ACOS

ACOS(n)

ADD_MONTHS

ADD_MONTHS(date, integer)

analytic_function

analytic_function([ arguments ])
   OVER (analytic_clause)

APPENDCHILDXML

APPENDCHILDXML
  ( XMLType_instance, XPath_string, value_expr [, namespace_string ] 
  )

ASCII

ASCII(char)

ASCIISTR

ASCIISTR(char)

ASIN

ASIN(n)

ATAN

ATAN(n)

ATAN2

ATAN2(n1 { , | / } n2)

AVG

AVG([ DISTINCT | ALL ] expr)
  [ OVER(analytic_clause) ]

BFILENAME

BFILENAME('directory', 'filename')

BIN_TO_NUM

BIN_TO_NUM(expr [, expr ]... )

BITAND

BITAND(expr1, expr2)

CARDINALITY

CARDINALITY(nested_table)

CAST

CAST({ expr | MULTISET (subquery) } AS type_name)

CEIL

CEIL(n)

CHARTOROWID

CHARTOROWID(char)

CHR

CHR(n [ USING NCHAR_CS ])

CLUSTER_ID

CLUSTER_ID ( [ schema . ] model mining_attribute_clause )

CLUSTER_PROBABILITY

CLUSTER_PROBABILITY ( [ schema . ] model
   [ , cluster_id ] mining_attribute_clause )

CLUSTER_SET

CLUSTER_SET ( [ schema . ] model
   [ , topN [ , cutoff ]
   ]
   mining_attribute_clause )

COALESCE

COALESCE(expr [, expr ]...)

COLLECT

COLLECT (column)

COMPOSE

COMPOSE(char)

CONCAT

CONCAT(char1, char2)

CONVERT

CONVERT(char, dest_char_set[, source_char_set ])

CORR

CORR(expr1, expr2)
   [ OVER (analytic_clause) ]

CORR_K, CORR_S

{ CORR_K | CORR_S }
   (expr1, expr2
    [, { COEFFICIENT
       | ONE_SIDED_SIG
       | ONE_SIDED_SIG_POS
       | ONE_SIDED_SIG_NEG
       | TWO_SIDED_SIG
       }
    ]
   )

COS

COS(n)

COSH

COSH(n)

COUNT

COUNT({ * | [ DISTINCT | ALL ] expr })
   [ OVER (analytic_clause) ]

COVAR_POP

COVAR_POP(expr1, expr2)
   [ OVER (analytic_clause) ]

COVAR_SAMP

COVAR_SAMP(expr1, expr2)
   [ OVER (analytic_clause) ]

CUME_DIST (aggregate)

CUME_DIST(expr[,expr ]...)
   WITHIN GROUP
   (ORDER BY expr [ DESC | ASC ]
                  [ NULLS { FIRST | LAST } ] 
             [, expr [ DESC | ASC ]
                     [ NULLS { FIRST | LAST } ]
             ]...
   )

CUME_DIST (analytic)

CUME_DIST( )
   OVER ([ query_partition_clause ] order_by_clause)

CURRENT_DATE

CURRENT_DATE

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP [ (precision) ]

CV

CV([ dimension_column ])

DBTIMEZONE

DBTIMEZONE

DECODE

DECODE(expr, search, result
             [, search, result ]...
       [, default ]
      )

DECOMPOSE

DECOMPOSE( string [ CANONICAL | COMPATIBILITY ] )

DELETXML

DELETEXML
  ( XMLType_instance, XPath_string
    [, namespace_string ]
  )

DENSE_RANK (aggregate)

DENSE_RANK(expr [, expr ]...) WITHIN GROUP
  (ORDER BY expr [ DESC | ASC ]
                 [ NULLS { FIRST | LAST } ]
            [,expr [ DESC | ASC ]
                   [ NULLS { FIRST | LAST } ]
            ]...
  )

DENSE_RANK (aggregate)

DENSE_RANK( )
   OVER([ query_partition_clause ] order_by_clause)

DEPTH

DEPTH(correlation_integer)

DEREF

DEREF(expr)

DUMP

DUMP(expr[, return_fmt
            [, start_position [, length ] ]
         ]
    )

EMPTY_BLOB, EMPTY_CLOB

{ EMPTY_BLOB | EMPTY_CLOB }( )

EXISTSNODE

EXISTSNODE
   (XMLType_instance, XPath_string
      [, namespace_string ]
   )

EXP

EXP(n)

EXTRACT (datetime)

EXTRACT( { { YEAR
           | MONTH
           | DAY
           | HOUR
           | MINUTE
           | SECOND
           }
         | { TIMEZONE_HOUR
           | TIMEZONE_MINUTE
           }
         | { TIMEZONE_REGION
           | TIMEZONE_ABBR
           }
         }
         FROM { datetime_value_expression
              | interval_value_expression
              }
       )

EXTRACT (XML)

EXTRACT(XMLType_instance, XPath_string
        [, namespace_string ]
       )

EXTRACTVALUE

EXTRACTVALUE
   (XMLType_instance, XPath_string
     [, namespace_string ]
   )

FEATURE_ID

FEATURE_ID ( [ schema . ] model mining_attribute_clause )

FEATURE_SET

FEATURE_SET ( [ schema . ] model
   [ , topN [ , cutoff ]
   ]
   mining_attribute_clause )

FEATURE_VALUE

FEATURE_VALUE ( [ schema . ] model
   [ , feature_id ] mining_attribute_clause )

FIRST

aggregate_function
   KEEP
   (DENSE_RANK FIRST ORDER BY
    expr [ DESC | ASC ]
         [ NULLS { FIRST | LAST } ]
    [, expr [ DESC | ASC ]
            [ NULLS { FIRST | LAST } ]
    ]...
   )
   [ OVER query_partition_clause ]

FIRST_VALUE

FIRST_VALUE (expr [ IGNORE NULLS ])
   OVER (analytic_clause)

FLOOR

FLOOR(n)

FROM_TZ

FROM_TZ (timestamp_value, time_zone_value)

GREATEST

GREATEST(expr [, expr ]...)

GROUP_ID

GROUP_ID( )

GROUPING

GROUPING(expr)

GROUPING_ID

GROUPING_ID(expr [, expr ]...)

HEXTORAW

HEXTORAW(char)

INITCAP

INITCAP(char)

INSERTCHILDXML

INSERTCHILDXML
  ( XMLType_instance, XPath_string, child_expr,
      value_expr [, namespace_string ] 
  )

INSERTXMLBEFORE

INSERTXMLBEFORE
   ( XMLType_instance, XPath_string,
      value_expr [, namespace_string ] 
   )

INSTR

{ INSTR
| INSTRB
| INSTRC
| INSTR2
| INSTR4
}
(string , substring [, position [, occurrence ] ])

ITERATION_NUMBER

ITERATION_NUMBER

LAG

LAG(value_expr [, offset ] [, default ])
   OVER ([ query_partition_clause ] order_by_clause)

LAST

aggregate_function KEEP
   (DENSE_RANK LAST ORDER BY
    expr [ DESC | ASC ]
         [ NULLS { FIRST | LAST } ]
    [, expr [ DESC | ASC ]
            [ NULLS { FIRST | LAST } ]
    ]...
   )
   [ OVER query_partition_clause ]

LAST_DAY

LAST_DAY(date)

LAST_VALUE

LAST_VALUE(expr [ IGNORE NULLS ])
   OVER (analytic_clause)

LEAD

LEAD(value_expr [, offset ] [, default ])
   OVER ([ query_partition_clause ] order_by_clause)

LEAST

LEAST(expr [, expr ]...)

LENGTH

{ LENGTH
| LENGTHB
| LENGTHC
| LENGTH2
| LENGTH4
}
(char)

LN

LN(n)

LNNVL

LNNVL(condition)

LOCALTIMESTAMP

LOCALTIMESTAMP [ (timestamp_precision) ]

LOG

LOG(n2, n1)

LOWER

LOWER(char)

LPAD

LPAD(expr1, n [, expr2 ])

LTRIM

LTRIM(char [, set ])

MAKE_REF

MAKE_REF({ table | view } , key [, key ]...)

MAX

MAX([ DISTINCT | ALL ] expr)
   [ OVER (analytic_clause) ]

MEDIAN

MEDIAN(expr) [ OVER (query_partition_clause) ]

MIN

MIN([ DISTINCT | ALL ] expr)
   [ OVER (analytic_clause) ]

MOD

MOD(n2, n1)

MONTHS_BETWEEN

MONTHS_BETWEEN(date1, date2)

NANVL

NANVL(n2, n1)

NCHR

NCHR(number)

NEW_TIME

NEW_TIME(date, timezone1, timezone2)

NEXT_DAY

NEXT_DAY(date, char)

NLS_CHARSET_DECL_LEN

NLS_CHARSET_DECL_LEN(byte_count, 'char_set_id')

NLS_CHARSET_ID

NLS_CHARSET_ID ( string )

NLS_CHARSET_NAME

NLS_CHARSET_NAME(number)

NLS_INITCAP

NLS_INITCAP(char [, 'nlsparam' ])

NLS_LOWER

NLS_LOWER(char [, 'nlsparam' ])

NLS_UPPER

NLS_UPPER(char [, 'nlsparam' ])

NLSSORT

NLSSORT(char [, 'nlsparam' ])

NTILE

NTILE(expr)
   OVER ([ query_partition_clause ] order_by_clause)

NULLIF

NULLIF(expr1, expr2)

NUMTODSINTERVAL

NUMTODSINTERVAL(n, 'interval_unit')

NUMTOYMINTERVAL

NUMTOYMINTERVAL(n, 'interval_unit')

NVL

NVL(expr1, expr2)

NVL2

NVL2(expr1, expr2, expr3)

ORA_HASH

ORA_HASH (expr [, max_bucket [, seed_value ] ])

PATH

PATH (correlation_integer)

PERCENT_RANK (aggregate)

PERCENT_RANK(expr [, expr ]...) WITHIN GROUP
   (ORDER BY
    expr [ DESC | ASC ]
         [NULLS { FIRST | LAST } ]
    [, expr [ DESC | ASC ]
            [NULLS { FIRST | LAST } ]
    ]...
   )

PERCENT_RANK (analytic)

PERCENT_RANK( )
   OVER ([ query_partition_clause ] order_by_clause)

PERCENTILE_CONT

PERCENTILE_CONT(expr) WITHIN GROUP
   (ORDER BY expr [ DESC | ASC ])
   [ OVER (query_partition_clause) ]

PERCENTILE_DISC

PERCENTILE_DISC(expr) WITHIN GROUP
   (ORDER BY expr [ DESC | ASC ])
   [ OVER (query_partition_clause) ]

POWER

POWER(n2, n1)

POWERMULTISET

POWERMULTISET(expr)

POWERMULTISET_BY_CARDINALITY

POWERMULTISET_BY_CARDINALITY(expr, cardinality)

PREDICTION

PREDICTION ( [ schema . ] model [ cost_matrix_clause ] mining_attribute_clause )

PREDICTION_COST

PREDICTION_COST ( [ schema . ] model [ , class ] cost_matrix_clause
 mining_attribute_clause )

PREDICTION_DETAILS

PREDICTION_DETAILS ( [ schema . ] model mining_attribute_clause )

PREDICTION_PROBABILITY

PREDICTION_PROBABILITY ( [ schema . ] model [ , class ] 
   mining_attribute_clause )

PREDICTION_SET

PREDICTION_SET ( [ schema . ] model [ , bestN [ , cutoff ] ] 
  [ cost_matrix_clause ] mining_attribute_clause )

PRESENTNNV

PRESENTNNV(cell_reference, expr1, expr2)

PRESENTV

PRESENTV(cell_reference, expr1, expr2)

PREVIOUS

PREVIOUS(cell_reference)

RANK (aggregate)

RANK(expr [, expr ]...) WITHIN GROUP
   (ORDER BY
    expr [ DESC | ASC ]
         [ NULLS { FIRST | LAST } ]
    [, expr [ DESC | ASC ]
            [ NULLS { FIRST | LAST } ]
    ]...
   )

RANK (analytic)

RANK( )
   OVER ([ query_partition_clause ] order_by_clause)

RATIO_TO_REPORT

RATIO_TO_REPORT(expr)
   OVER ([ query_partition_clause ])

RAWTOHEX

RAWTOHEX(raw)

RAWTONHEX

RAWTONHEX(raw)

REF

REF (correlation_variable)

REFTOHEX

REFTOHEX (expr)

REGEXP_INSTR

REGEXP_INSTR (source_char, pattern
              [, position
                 [, occurrence
                    [, return_option
                       [, match_parameter ]
                    ]
                 ]
              ]
             )

REGEXP_REPLACE

REGEXP_REPLACE(source_char, pattern
               [, replace_string
                  [, position
                     [, occurrence
                        [, match_parameter ]
                     ]
                  ]
               ]
              )

REGEXP_SUBSTR

REGEXP_SUBSTR(source_char, pattern
              [, position
                 [, occurrence
                    [, match_parameter ]
                 ]
              ]
             )

REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY

{ REGR_SLOPE 
| REGR_INTERCEPT 
| REGR_COUNT 
| REGR_R2 
| REGR_AVGX
| REGR_AVGY 
| REGR_SXX 
| REGR_SYY 
| REGR_SXY
}
(expr1 , expr2)
[ OVER (analytic_clause) ]

REMAINDER

REMAINDER(n2, n1)

REPLACE

REPLACE(char, search_string
        [, replacement_string ]
       )

ROUND (date)

ROUND(date [, fmt ])

ROUND (number)

ROUND(n [, integer ])

ROW_NUMBER

ROW_NUMBER( )
   OVER ([ query_partition_clause ] order_by_clause)

ROWIDTOCHAR

ROWIDTOCHAR(rowid)

ROWIDTONCHAR

ROWIDTONCHAR(rowid)

RPAD

RPAD(expr1 , n [, expr2 ])

RTRIM

RTRIM(char [, set ])

SCN_TO_TIMESTAMP

SCN_TO_TIMESTAMP(number)

SESSIONTIMEZONE

SESSIONTIMEZONE

SET

SET (nested_table)

SIGN

SIGN(n)

SIN

SIN(n)

SINH

SINH(n)

SOUNDEX

SOUNDEX(char)

SQRT

SQRT(n)

STATS_BINOMIAL_TEST

STATS_BINOMIAL_TEST(expr1, expr2, p
                    [, { TWO_SIDED_PROB
                       | EXACT_PROB
                       | ONE_SIDED_PROB_OR_MORE
                       | ONE_SIDED_PROB_OR_LESS
                       }
                    ]
                   )

STATS_CROSSTAB

STATS_CROSSTAB(expr1, expr2
               [, { CHISQ_OBS
                  | CHISQ_SIG
                  | CHISQ_DF
                  | PHI_COEFFICIENT
                  | CRAMERS_V
                  | CONT_COEFFICIENT
                  | COHENS_K
                  }
               ]
              )

STATS_F_TEST

STATS_F_TEST(expr1, expr2
             [, { { STATISTIC
                  | DF_NUM
                  | DF_DEN
                  | ONE_SIDED_SIG
                  } expr3
                | TWO_SIDED_SIG
                }
             ]
            )

STATS_KS_TEST

STATS_KS_TEST(expr1, expr2
              [, { STATISTIC | SIG } ]
             )

STATS_MODE

STATS_MODE(expr)

STATS_MW_TEST

STATS_MW_TEST(expr1, expr2
              [, { STATISTIC
                 | U_STATISTIC
                 | ONE_SIDED_SIG "expr3"
                 | TWO_SIDED_SIG
                 }
              ]
             )

STATS_ONE_WAY_ANOVA

STATS_ONE_WAY_ANOVA(expr1, expr2
                    [, { SUM_SQUARES_BETWEEN
                       | SUM_SQUARES_WITHIN
                       | DF_BETWEEN
                       | DF_WITHIN
                       | MEAN_SQUARES_BETWEEN
                       | MEAN_SQUARES_WITHIN
                       | F_RATIO
                       | SIG
                       }
                    ]
                   )

STATS_T_TEST_INDEP, STATS_T_TEST_INDEPU, STATS_T_TEST_ONE, STATS_T_TEST_PAIRED

{ STATS_T_TEST_INDEP
| STATS_T_TEST_INDEPU
| STATS_T_TEST_ONE
| STATS_T_TEST_PAIRED
}
(expr1, expr2
  [, { { STATISTIC
       | ONE_SIDED_SIG
       } expr3
     | TWO_SIDED_SIG
     | DF
     }
  ]
)

STATS_WSR_TEST

STATS_WSR_TEST(expr1, expr2
               [, { STATISTIC
                  | ONE_SIDED_SIG
                  | TWO_SIDED_SIG
                  }
               ]
              )

STDDEV

STDDEV([ DISTINCT | ALL ] expr)
   [ OVER (analytic_clause) ]

STDDEV_POP

STDDEV_POP(expr)
   [ OVER (analytic_clause) ]

STDDEV_SAMP

STDDEV_SAMP(expr)
   [ OVER (analytic_clause) ]

SUBSTR

{ SUBSTR
| SUBSTRB
| SUBSTRC
| SUBSTR2
| SUBSTR4
}
(char, position [, substring_length ])

SUM

SUM([ DISTINCT | ALL ] expr)
   [ OVER (analytic_clause) ]

SYS_CONNECT_BY_PATH

SYS_CONNECT_BY_PATH(column, char)

SYS_CONTEXT

SYS_CONTEXT('namespace', 'parameter' [, length ])

SYS_DBURIGEN

SYS_DBURIGEN({ column | attribute }
             [ rowid ]
               [, { column | attribute }
                  [ rowid ]
               ]...
             [, 'text ( )' ]
            )

SYS_EXTRACT_UTC

SYS_EXTRACT_UTC(datetime_with_timezone)

SYS_GUID

SYS_GUID( )

SYS_TYPEID

SYS_TYPEID(object_type_value)

SYS_XMLAGG

SYS_XMLAGG(expr [, fmt ])

SYS_XMLGEN

SYS_XMLGEN(expr [, fmt ])

SYSDATE

SYSDATE

SYSTIMESTAMP

SYSTIMESTAMP

TAN

TAN(n)

TANH

TANH(n)

TIMESTAMP_TO_SCN

TIMESTAMP_TO_SCN(timestamp)

TO_BINARY_DOUBLE

TO_BINARY_DOUBLE(expr [, fmt [, 'nlsparam' ] ])

TO_BINARY_FLOAT

TO_BINARY_FLOAT(expr [, fmt [, 'nlsparam' ] ])

TO_CHAR (character)

TO_CHAR(nchar | clob | nclob)

TO_CHAR (datetime)

TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])

TO_CHAR (number)

TO_CHAR(n [, fmt [, 'nlsparam' ] ])

TO_CLOB

TO_CLOB(lob_column | char)

TO_DATE

TO_DATE(char [, fmt [, 'nlsparam' ] ])

TO_DSINTERVAL

TO_DSINTERVAL(char [, 'nlsparam' ])

TO_LOB

TO_LOB(long_column)

TO_MULTI_BYTE

TO_MULTI_BYTE(char)

TO_NCHAR (character)

TO_NCHAR({char | clob | nclob})

TO_NCHAR (datetime)

TO_NCHAR({ datetime | interval }
         [, fmt [, 'nlsparam' ] ]
        )

TO_NCHAR (number)

TO_NCHAR(n [, fmt [, 'nlsparam' ] ])

TO_NCLOB

TO_NCLOB(lob_column | char)

TO_NUMBER

TO_NUMBER(expr [, fmt [, 'nlsparam' ] ])

TO_SINGLE_BYTE

TO_SINGLE_BYTE(char)

TO_TIMESTAMP

TO_TIMESTAMP(char [, fmt [, 'nlsparam' ] ])

TO_TIMESTAMP_TZ

TO_TIMESTAMP_TZ(char [, fmt [, 'nlsparam' ] ])

TO_YMINTERVAL

TO_YMINTERVAL(char)

TRANSLATE

TRANSLATE(expr, from_string, to_string)

TRANSLATE ... USING

TRANSLATE ( char USING
          { CHAR_CS | NCHAR_CS }
          )

TREAT

TREAT(expr AS [ REF ] [ schema. ]type)

TRIM

TRIM([ { { LEADING | TRAILING | BOTH }
         [ trim_character ]
       | trim_character
       }
       FROM 
     ]
     trim_source
    )

TRUNC (date)

TRUNC(date [, fmt ])

TRUNC (number)

TRUNC(n1 [, n2 ])

TZ_OFFSET

TZ_OFFSET({ 'time_zone_name'
          | '{ + | - } hh : mi'
          | SESSIONTIMEZONE
          | DBTMEZONE
          }
         )

UID

UID

UNISTR

UNISTR( string )

UPDATEXML

UPDATEXML
      (XMLType_instance,
        XPath_string, value_expr
          [, XPath_string, value_expr ]...
        [, namespace_string ]
      )

UPPER

UPPER(char)

USER

USER

user-defined function

[ schema. ]
{ [ package. ]function | user_defined_operator }
[ @ dblink. ]
[ ([ DISTINCT | ALL ] expr [, expr ]...) ]

USERENV

USERENV('parameter')

VALUE

VALUE(correlation_variable)

VAR_POP

VAR_POP(expr) [ OVER (analytic_clause) ]

VAR_SAMP

VAR_SAMP(expr) [ OVER (analytic_clause) ]

VARIANCE

VARIANCE([ DISTINCT | ALL ] expr)
        [ OVER (analytic_clause) ]

VSIZE

VSIZE(expr)

WIDTH_BUCKET

WIDTH_BUCKET
   (expr, min_value, max_value, num_buckets)

XMLAGG

XMLAGG(XMLType_instance [ order_by_clause ])

XMLCOLATTVAL

XMLCOLATTVAL
  (value_expr [ AS c_alias ]
    [, value_expr [ AS c_alias ]
      ]...
  )

XMLCOMMENT

XMLCOMMENT ( value_expr )

XMLCDATA

XMLCDATA ( value_expr )

XMLCONCAT

XMLCONCAT(XMLType_instance [, XMLType_instance ]...)

XMLELEMENT

XMLELEMENT 
 ( [ NAME ] identifier
    [, XML_attributes_clause ]
    [, value_expr [ AS c_alias ]
      [, value_expr [ AS c_alias ]
    ]...
 )

XMLFOREST

XMLFOREST
  ( value_expr [ AS c_alias ]
    [, value_expr [ AS c_alias ]
      ]...
  )

XMLPARSE

XMLPARSE
  ({ DOCUMENT | CONTENT } value_expr [ WELLFORMED ]
  )

XMLPI

XMLPI 
 (
  [ NAME ] identifier
  [, value_expr ]
 )

XMLQUERY

XMLQUERY
 ( XQuery_string 
   [ XML_passing_clause ] 
   RETURNING CONTENT 
 )

XMLROOT

XMLROOT
  ( value_expr, VERSION 
  { value_expr | NO VALUE }
  [, STANDALONE { YES | NO | NO VALUE } ]
  )

XMLSEQUENCE

XMLSEQUENCE( XMLType_instance
           | sys_refcursor_instance [, fmt ]
           )

XMLSERIALIZE

XMLSERIALIZE
  ( { DOCUMENT | CONTENT } value_expr
      [ AS datatype ]
  )

XMLTABLE

XMLTABLE
 ( 
  [ XML_namespaces_clause , ] XQuery_string XMLTABLE_options 
 )

XMLTABLE_options

[ XML_passing_clause ] [ COLUMNS XML_table_column
                                 [, XML_table_column 
                                 ]...
                       ]

XMLTRANSFORM

XMLTRANSFORM(XMLType_instance, XMLType_instance)