Skip Headers

Oracle® Database SQL Quick Reference
10g Release 1 (10.1)
Part Number B10758-01
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
Feedback
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.

Table 2-1 shows each SQL function and its related syntax. Refer to Chapter 5, " Subclauses " for the syntax of the subclauses found in the following table.


See Also:

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

Table 2-1 Syntax for SQL Functions

SQL Function Syntax
 
ABS

ABS(n)


ACOS

ACOS(n)


ADD_MONTHS

ADD_MONTHS(date, integer)


analytic_function

analytic_function([ arguments ])
   OVER (analytic_clause)


ASCII

ASCII(char)


ASCIISTR

ASCIISTR('char')


ASIN

ASIN(n)


ATAN

ATAN(n)


ATAN2

ATAN2(n { , | / } m)


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


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


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 (analytic)

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
   )


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)


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(m, n)


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(m, n)


MONTHS_BETWEEN

MONTHS_BETWEEN(date1, date2)


NANVL

NANVL(m, n)


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(text)


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(m, n)


POWERMULTISET

POWERMULTISET(expr)


POWERMULTISET_BY_CARDINALITY

POWERMULTISET_BY_CARDINALITY(expr, cardinality)


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_string, pattern
              [, position
                 [, occurrence
                    [, return_option
                       [, match_parameter ]
                    ]
                 ]
              ]
             )


REGEXP_REPLACE

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


REGEXP_SUBSTR

REGEXP_SUBSTR(source_string, 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(m, n)


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
                | 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
                 | 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
     | DF
     | ONE_SIDED_SIG
     | TWO_SIDED_SIG
     }
  ]
)


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
}
(string, 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}
         [, fmt [, 'nlsparam' ] ]
        )


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(text 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(n [, m ])


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


XMLCONCAT

XMLCONCAT(XMLType_instance [, XMLType_instance ]...)


XMLELEMENT

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


XMLFOREST

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


XMLSEQUENCE

XMLSEQUENCE( XMLType_instance
           | sys_refcursor_instance [, fmt ]
           )


XMLTRANSFORM

XMLTRANSFORM(XMLType_instance, XMLType_instance)


Go to previous page
Previous		 Go to next page
Next
 Oracle
Copyright © 2003 Oracle Corporation
All Rights Reserved.
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
Feedback