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 value containing information on 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 selected number of decimal positions.

• 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 a string in which each search_str is replaced by the 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. 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. This function returns a date which is the result of a procedure registered in the base.

  • CALL_NUMBER. This functions returns a number which is the result of a procedure registered in the base.

  • CALL_VARCHAR. This functions returns a string which is the result of a procedure registered in the base.

See also:

Table | Table Constraints