[SOLVED] CS SQL database Oracle Functions

Oracle Functions
Reference: SQL Manual 12C available from Moodle Chapter 7 pp 7-1 7-447 or online at: https://docs.oracle.com/database/121/SQLRF/toc.htm
Oracle functions are useful for manipulating data by decomposing data elements. They use numerical, date or string values and may appear in a SQL statement wherever a value or attribute is used. Functions are categorised according to their operand types:
Arithmetic for manipulation of numerical data
Text for manipulation of alphanumeric data
Date for manipulation of date/time-related data
General for manipulation of any data type
Conversion for manipulation of data type conversions, and
Group for manipulation sets of values
The examples below show the uses for some of the more common Oracle SQL function
Arithmetic Functions
abs(n)
ceil(n)
floor(n)
Largest integer equal to or less than n
select floor(10.6) from dual;
mod(m,n)
Remainder of m divided by n. If n=0, then m is returned
select mod(7,5) from dual;
power(m,n)
Number m raised to the power of n
select power(3,2) from dual;
round(n,m)
Results rounded to m places to the right of decimal point
select round(15.193,1) from dual;
sign(n)
If n=0, returns 0; if n>0, returns 1; if n<0, returns -1select sign(12 – 45) from dual;The column’s absolute valueselect abs(sallower – salupper) from salgrade;Nearest whole integer greater than or equal to numberselect ceil(10.6) from dual;Page 1 of 5sqrt(n)trunc(n,m)Truncates n to m decimal points, if m is omitted then n is truncated to 0 placesselect trunc(15.79,1) from dual;select trunc(15.79) from dual;Text Functionsinitcap(char)Changes the first character of each character string to uppercaseselect initcap(‘mr teplow’) from dual;lower(char), upper(char)Makes the entire string lowercase/uppercaseselect lower(ename) from employee;replace(char, str1, str2)Character string with every occurrence of str1 being replaced with str2select replace(‘jack and jue’,’j’,’bl’) from dual;substr(char,m,n)Picks off part of the character string char starting in position m for n charactersselect substr(‘ABCDEF’,2,1) from dual;length(char)Length of charselect length(‘Anderson’) from dual;str1 || str2Concatenates two character fields togetherselect deptname || ‘, ‘ || deptlocation as “Department Name andLocation”from department;lpad(char,n,char2)/rpad(char,n,char2)Pads char left/right to size n using char2select lpad(‘Page 1’, 15, ‘*’) as “Lpad example”from dual;select rpad(‘Page 1’, 15, ‘*’) as “Rpad example”from dual;Square root of nselect sqrt(120) from dual;select round(sqrt(120),2) from dual; Page 2 of 5 ltrim(char[, k]), rtrim(char[, k])remove characters from the left/right of char, until the first character not in k – if k is not specified blanks are trimmedselect ltrim(‘Intro to SQL’, ‘InorSt ‘) from dual;Date Functionslast_dayLast day of the monthselect last_day(SYSDATE) from dual;add_months(d,n)Adds or subtracts n months from date dselect add_months(SYSDATE, 2) from dual;months_between(f,s)Difference in months between date f and date sselect months_between(sysdate, ‘1-JAN-2006’) from dual;next_day(d,day)Date that is the specified day of the week after dselect next_day(SYSDATE, ‘Monday’) from dual;extract(c from d)Extract date/time component c from expression dselect bdate,extract (year from bdate) AS year_of_birth,extract (month from bdate) AS month_of_birth,extract (day from bdate) AS day_of_birthfrom employee;General Functionsgreatest(a, b, …)greatest value of the function argumentsleast(a, b, …)least value of the function argumentsselect greatest(12*6, 148/2, 73), least(12*6, 148/2, 73) from dual;nullif(a, b)NULL if a = b; otherwise aNVL(x, y)y if x is NULL; otherwise xPage 3 of 5 decode (x, a1, b1, a2, b2, …., an, bn [, y])b1 if x = a1, b2 if x = a2, …. bn if x = an, and otherwise y (or default:NULL)Conversion Functionsto_charconverts any data type to character data using a format model (picture) eg. ‘DD Mon YYYY’ or ‘$9999.99’select to_char(sysdate,’DD Mon YYYY’) from dual;to_numberconverts a valid set of numeric character data to number data typeselect 123, to_char(123, ‘$9999.99’), to_number(‘123’) from dual;to_dateconverts character data of the proper format to date data typeuses format models – a character literal eg. dd-Mon-yyyy to control how Oracle interprets the stringFormat Model elements for date/time conversion:Y or YY or YYYYLast one, two or four digits of year.select to_char(sysdate, ‘YYYY’) from dual;QMM, RMMonth(01-12), Roman numeral month (eg. IV for April)select to_char(sysdate, ‘MM) from dual;select to_char(sysdate, ‘RM’) from dual;MonthWW, WWeek of year, Week of monthselect to_char(sysdate, ‘WW’) from dual;select to_char(sysdate, ‘W’) from dual;DDD, DD, DDay of the year, month, weekselect to_char(sysdate, ‘DDD’) from dual;select to_char(sysdate, ‘DD’) from dual;Quarter of year (Jan thru March = 1)select to_char(sysdate, ‘Q’) from dual;Name of monthselect to_char(sysdate, ‘Month’) from dual;Page 4 of 5select to_char(sysdate, ‘D’) from dual;DY, DAYAbbreviated, full name of dayselect to_char(sysdate, ‘DY’) from dual;HH or HH12Hour of day using 12 hour formatselect to_char(sysdate, ‘HH’) from dual;HH24MISSHour of day using 24-hour clockMinutes (0-59)Seconds (0-59)select to_char(sysdate, ‘HH24:MI:SS’) from dual;Page 5 of 5