Scalar Functions

Scalar functions receive an input value, and based on this input value the result is delivered. This function can be used with constant values, the column elements of a table (view) as well as with compound value expressions.

Example
SELECT SIN(1);
SELECT LENGTH(s) FROM t;
SELECT EXP(1+ABS(n)) FROM t;

Scalar functions usually expect a special data type for their arguments. If it is not specified, then an implicit conversion of the data type is attempted, or an error message is displayed.

Numeric Function

Numeric functions are given a numeric value as input and typically deliver a numeric value as output.

The following are the numeric functions supported:

ABS

ACOS

ASIN

ATAN

ATAN2

CEIL[ING]

COS

COSH

COT

DEGREES

DIV

EXP

FLOOR

LN

LOG

LOG10

LOG2

MIN_SCALE
MOD

PI

POWER

RADIANS

RAND[OM]

ROUND (number)

SIGN

SIN

SINH
SQRT
TAN
TANH
TO_CHAR (number)
TO_NUMBER
TRUNC[ATE] (number)

String Functions

String functions can either return a string (for example, LPAD) or a numeric value (for example, LENGTH).

ASCII
BIT_LENGTH
CHARACTER_LENGTH
CH[A]R
COLOGNE_PHONETIC
CONCAT
DUMP
EDIT_DISTANCE
INITCAP
INSERT
INSTR
LCASE

LEFT

LENGTH

LOCATE

LOWER
LPAD

LTRIM

MID

OCTET_LENGTH

POSITION

REGEXP_INSTR

REGEXP_REPLACE

REGEXP_SUBSTR

REPEAT

REPLACE

REVERSE

RIGHT

RPAD

RTRIM

SOUNDEX

SPACE

SUBSTR[ING]

TO_CHAR (datetime)

TO_CHAR (number)

TO_NUMBER

TRANSLATE

TRIM

TYPEOF

UCASE

UNICODE

UNICODECHR

UPPER

Date/Time Functions

Date/Time functions manipulate the DATE, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, and INTERVAL data types.

ADD_DAYS
ADD_HOURS
ADD_MINUTES
ADD_MONTHS
ADD_SECONDS
ADD_WEEKS
ADD_YEARS
CONVERT_TZ
CURDATE
CURRENT_DATE
CURRENT_TIMESTAMP
DATE_TRUNC
DAY
DAYS_BETWEEN
DBTIMEZONE
EXTRACT
FROM_POSIX_TIME
HOUR
HOURS_BETWEEN
LOCALTIMESTAMP
MINUTE
MINUTES_BETWEEN
MONTH
MONTHS_BETWEEN
NOW
NUMTODSINTERVAL
NUMTOYMINTERVAL
POSIX_TIME
ROUND (datetime)
SECOND
SECONDS_BETWEEN
SESSIONTIMEZONE
SYSDATE
SYSTIMESTAMP
TO_CHAR (datetime)
TO_DATE
TO_DSINTERVAL
TO_TIMESTAMP
TO_YMINTERVAL
TRUNC[ATE] (datetime)
WEEK
YEAR
YEARS_BETWEEN

Geospatial Functions

To analyze geospatial data, there are many functions available. For more information on geospatial data, refer to the Geospatial Data section.

ST_*

Bitwise Function

Bitwise functions can compute bit operations on numerical values.

BIT_AND
BIT_CHECK
BIT_LROTATE
BIT_LSHIFT
BIT_NOT
BIT_OR
BIT_RROTATE
BIT_RSHIFT
BIT_SET
BIT_TO_NUM
BIT_XOR

Conversion Functions

Conversion functions can be used to convert values to other data types.

CAST
CONVERT
IS_*
NUMTODSINTERVAL
NUMTOYMINTERVAL
TO_CHAR (datetime)
TO_CHAR (number)
TO_DATE
TO_DSINTERVAL
TO_NUMBER
TO_TIMESTAMP
TO_YMINTERVAL

Functions for Hierarchical Queries

The following functions can be used in combination with CONNECT BY queries.

CONNECT_BY_ISCYCLE
CONNECT_BY_ISLEAF
LEVEL
SYS_CONNECT_BY_PATH

JSON Functions

JSON functions extract values from JSON objects stored as strings.

JSON_VALUE
JSON_EXTRACT

Other Scalar Functions

Those functions that cannot be allocated to one of the above categories are listed here.

CASE
COALESCE
CURRENT_SCHEMA
CURRENT_SESSION
CURRENT_STATEMENT
CURRENT_USER
DECODE
GREATEST
HASH_MD5
HASH_SHA[1]
HASH_SHA256
HASH_SHA512
HASH_TIGER
HASHTYPE_MD5
HASHTYPE_SHA[1]
HASHTYPE_SHA256
HASHTYPE_SHA512
HASHTYPE_TIGER

IPROC
LEAST
NULLIF
NULLIFZERO
NPROC
NVL
NVL2
ROWNUM
ROWID
SCOPE_USER
SESSION_PARAMETER
SYS_GUID
USER
VALUE2PROC
ZEROIFNULL