For purposes of interfacing, writing scripts or end user reporting, there are a number of data base functions that can be used - both from Oracle and those created as part of the application. As well, customers could create their own custom data base function for this purpose.
The functions listed below are those found to be useful in the creation of interfaces, conversion, script writing or reporting. It is not an exhaustive list, and can certainly be extended by a customer adding their own functions.
Calling Functions in SQL Loader |
Calling Functions in IDIF (Derivation Expression) – export interfaces |
Wiki supplied Functions#
Oracle Built-in Functions#
Conversion Functions#
Text Functions#
CONCAT | Concatenation of two strings - usually easier to use two pipes (eg. ||) |
SUBSTR | Extracting a substring out of a larger text string |
INITCAP , UPPER and LOWER | Reformatting a text string's capital letters |
INSTR | Determining if one string is inside another |
LENGTH | Determining the length of the string |
LPAD and RPAD | Left- and Right-padding character fields with another value |
TRIM | Trimming off characters (usually blanks) |
LTRIM and RTRIM | Trimming off characters (usually blanks) from teh left or right side of the string |
REPLACE | Replace one value in a string with another |
Numeric Functions#
ABS | Absolute Value (removing the - on negative values) |
CEIL | Rounding a number up to the next integer |
FLOOR | Rounding a number down to the previous integer |
MOD | Modulus division (determining the remainder) |
ROUND | Rounding a number up/down and to a specific number of decimal places |
TRUNC | Truncating the decimal part of a number |
SIGN | Determining the +/- sign of a number |
Date Functions#
Miscellaneous Functions#
DECODE | Translating a finite set of values from one set to another |
GREATEST and LEAST | Determining the greatest (maximum) and least (minimum) value between sets of values |
NVL | testing for and resolving NULL values |
NVL2 | testing for a resolving NULL and NOT NULL values |
COALESCE | testing for and resolving multiple NULL values |
Click to create a new notes page
Screen captures are meant to be indicative of the concept being presented and may not reflect the current screen design.
If you have any comments or questions please email the Wiki Editor
All content © High Line Corporation