Built-in SQL Functions

You can use the following built-in SQL functions on creating constraints:

• Functions returning system variables:

• SYSDATE. It returns current date and time.

• USERENV. It returns the value containing information about the current session.

• Numerical functions:

• ABS. It returns absolute value of a number.

• MOD. It returns remainder after dividing one value by another value.

• ROUND. It rounds off value to selected number of positions.

• SIGN. It returns sign of the values specified as a parameter.

• TRUNC. It truncates a value to the specified number of decimal places.

• Text functions:

• CHR. It returns a symbol for the specified symbol number in the ASCII table.

• CONCAT3. It returns a string which is the result of concatenation ("adhesion") of three lines.

• UPPER. It returns a string where all symbols are typed using the upper case.

• LENGTH. It returns the string length in symbols.

• SUBSTR. It returns a part of string starting from a symbol that has the "a" number and the "b" length.

• INSTR. It returns the position where a substring enters into the string.

• LPAD. It returns a string to the left-hand side of which symbols from another string are added to enlarge it to the required length.

• RPAD. It returns a string to the right-hand side of which symbols from another string are added to enlarge it to the required length.

• LTRIM. It returns a string with deleted left symbols occurring in the specified substring.

• RTRIM. It returns a string with deleted right symbols occurring in the specified substring.

• REPLACE. It returns the string, in which each search_str occurrence is replaced by replace_str.

• Functions used for working with dates:

• ADD_MONTHS. It returns the date increased by the specified number of months.

• Data transformation functions:

• TO_CHAR. It transforms a date or a value into a string of symbols.

• TO_LONGCHAR. It converts input value into a long string (LONGCHAR).

• TO_DATE. It transforms a string into a date.

• TO_NUMBER. It transforms a string into a numeric value.

• Other functions:

• DECODE. It is an analog of a sequence of the nested statements IF-THEN-ELSE.

• NVL. It returns "expr2", if "expr1" contains the NULL value, otherwise it returns "expr1".

• CALL_DATE. It returns a date, which is the result of a procedure registered in the database.

• CALL_NUMBER. It returns a number, which is the result of a procedure registered in the database.

• CALL_VARCHAR. It returns a string, which is the result of a procedure registered in the database.

See also:

Table | Table Constraints

Copy page URL

Back to top

To report a found bug, please, select the text with the bug and click Report a Bug or CTRL+ENTER.

Help system 10.5 of 04/10/2024, © FORESIGHT