SYSDATE returns a DATE that represents the date and time set on the operating system of the machine Oracle is installed on. The format of SYSDATE is controlled by the NLS_DATE_FORMAT session parameter. Example: SELECT SYSDATE FROM DUAL; SYSDATE ——————-
Lpad – Oracle SQL Function
LPAD returns a VARCHAR2 string that is arg1 left padded with arg3 for a length of n. If arg3 is not provided Oracle will pad with blank spaces. Example: SELECT LPAD(’10 + signs’,10,’+’) as RESULT from DUAL; RESULT —————— ++++++++++10
DBTIMEZONE – Oracle SQL Function
DBTIMEZONE returns the current database timezone Example: SELECT DBTIMEZONE FROM DUAL; DBTIME ———— +00:00
Power – Oracle SQL Function
POWER returns the first argument raised to the power of the second. The arguments can be a numeric value or any type that can be implicitly converted to a number. Using POWER is a good way of turning a LOG
Systimestamp – Oracle SQL Function
SYSTIMESTAMP returns a TIMESTAMP WITH TIME ZONE result from the underlying operating system date, timestamp, fractional seconds and time zone. Example: SELECT SYSTIMESTAMP FROM DUAL; SYSTIMESTAMP ————————————- 13-SEP-05 10.40.32.818000 PM -05:00
Ltrim – Oracle SQL Function
LTRIM returns a VARCHAR2 string that is the same as arg1 with the characters in arg2 removed. LTRIM keeps scanning from the left most character and keeps removing characters until none match arg2. Example: SELECT LTRIM(‘xxxx-AA’,’x’) as RESULT from dual;
EXTRACT – Oracle SQL Function
EXTRACT allows you to extract parts of a date expression. You may want to extract the year from a date string, or minute value in a date-time expression. Valid extractions include YEAR, MONTH, DAY,HOUR, MINUTE,SECOND,TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION and TIMEZONE_ABBR Example:
Remainder – Oracle SQL Function
REMAINDER returns the remainder of the 1st argument divided by the 2nd argument. Remainder is similar to MOD except thaqt REMAINDER uses ROUND in its calculations, whereas MOD uses FLOOR. Example: SELECT REMAINDER(10,3) as remainder_value from dual; REMAINDER_VALUE ——————————– 1
Asciistr – Oracle SQL Function
ASCIISTR takes a string argument and returns the ASCII equivalent. If any of the characters in the string are non ASCII, they are converted to the UTF-16xxxx format. Syntax: ASCIISTR(string) Example: SELECT ASCIISTR(‘a non-ascii char ŷ’ as RESULT FROM DUAL;
Rpad – Oracle SQL Function
RPAD returns a VARCHAR2 string that is arg1 right padded with arg3 for a length of n. If arg3 is not provided Oracle will pad with blank spaces. Example: SELECT RPAD(’10 + spaces’,10,’+’) as RESULT from dual;