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
MONTHS_BETWEEN – Oracle SQL Function
MONTHS_BETWEEN returns an integer specfiying the number of months between two dates. The result can be either positive or negative depending on which date is greater. Example: SELECT MONTHS_BETWEEN(10-JAN-2036′, ’10-JAN-2004′ ) AS RESULT FROM DUAL; RESULT ——————- 384
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;
NEW_TIME – Oracle SQL Function
NEW_TIME returns the date and time in timt zone ‘tz2’ based on the time zone ‘tz1’ and the date argument ‘the_date’. Syntax: NEW_TIME(the_date, tz1, tz2) You must set the NLS_DATE_FORMAT parameter to a 24-hour format before you execute the NEW_TIME
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;
Next_day – Oracle SQL Function
NEXT_DAY returns the next day specified in the day_to_find argument from the date argument. day_to_find must be a string denoting a valid day of the week. Syntax: NEXT_DAY(date,day_to_find) Example: SELECT NEXT_DAY(’23-OCT-2004′,’FRIDAY’) AS RESULT FROM DUAL; RESULT —————- 29-OCT-2004 00:00:00
Avg – Oracle SQL Function
AVG returns the average value of a column. Syntax: AVG(column name) e.g. SELECT AVG(SALARY) FROM EMPLOYEE
Corr – Oracle SQL Function
CORR returns the coefficient of a pair of numbers. Syntax: CORR(expression1, expression2) Both expressions can NUMBER values or any value that can be converted to a NUMBER. e.g. SELECT id, CORR(list_price, min_price) as RESULTS FROM product_info GROUP BY supplier_id NOTE:
Count – Oracle SQL Function
COUNT returns the number of rows returned by the query where the expression is not NULL. Syntax: COUNT([DISTINCT] expression) If you pass the count function the (*) wildcard then it will return the count of all rows, including the rows
Covar_pop – Oracle SQL Function
COVAR_POP returns the population covariance of a pair of numbers. Syntax: COVAR_POP(expression1, expression2) Example: SELECT id, COVAR_POP(list_price,min_price) as RESULT FROM product_information GROUP BY id;