SQL Functions SQL Functions SQL functions are built

SQL Functions

SQL Functions • SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements. • These functions are use full for performing processing on string, numeric or date type data.

Two Types of SQL Functions Single-row Functions (Scalar Function) Multiple-row Functions (Aggregate Or Group function)

SQL Functions Single-Row/Scalar Functions: • Act on each row returned • Return one result per row Multiple Row/Group/Aggregate Functions • Act on set of rows • Return one result for set of rows

Single-Row Functions Character Conversion Single-row functions Date Number

Character/String Functions • • • LOWER UPPER INITCAP SUBSTR INSTR ASCII TRANSLATE CONCAT LENGTH LTRIM RTRIM LPAD RPAD VSIZE

Character/String Functions • LOWER: - Returns the argument in lowercase. syntax: LOWER(char||field name) example: select LOWER(‘MYSQL') from dual; -> mysql • UPPER: -Convert argument to uppercase. syntax: UPPER(char||field name) example: SELECT UPPER('Hej') from dual; -> HEJ

Character/String Functions • INITCAP: -Returns string with the first letter of each word in upper case. Syntax: INITCAP(char||field_name) example: select INITCAP(‘my sql') from dual; -> My Sql • SUBSTR: – Returns a sub part of string with respect to position and number of string. it takes string, position and no_of_char as argument. Syntax: SUBSTR(string||field, position, no_of_char) Example: select substr(name, 3, 2) from demo;

SELECT ASCII('dx'); -> 100 Character/String Functions • INSTR: - Returns the position of the first occurrence of substring substr in string str. syntax: INSTR(str 1, st 2, [start_posi]) example: SELECT INSTR('foobarbar', 'bar'); ->4 • ASCII: -Return ascii numeric value of character syntax: ASCII(char) example: SELECT ASCII(‘d'); -> 100

SELECT ASCII('dx'); -> 100 Character/String Functions • TRANSLATE: - TRANSLATE returns string with all occurrences of each character in to_replace_str replaced by its corresponding replacement_str. syntax: TRANSLATE(string to_replace_str, replacement_str) example: SELECT TRANSLATE(5 my. Number 123', ‘ 512‘, ’ 789’); ->7 my. Number 893 • CONCAT: -Returns the string that results from concatenating the arguments. syntax: CONCAT(str 1, str 2) example: SELECT CONCAT(‘My’, ’Sql’); -> My. Sql

SELECT ASCII('dx'); -> 100 Character/String Functions • LENGTH: - Returns the length of a string. syntax: LENGTH(‘str’) example: SELECT LENGTH('text') from dual; ->4 • LTRIM: - Returns the string str with leading space characters or specified character removed. syntax: LTRIM(str, [char]) example: SELECT LTRIM(' My. SQL'); -> My. SQL SELECT LTRIM(‘ANISHA’, ’A’) from dual; ->NISHA

SELECT ASCII('dx'); -> 100 Character/String Functions • RTRIM: - Returns the string str with trailing space characters or specified character removed from right side. syntax: RTRIM(str, [char]) example: SELECT RTRIM('My. SQL '); -> My. SQL SELECT LTRIM(‘ANISHA’, ’A’) from dual; ->ANISH • TRIM: - removes the leading, trailing, both space characters or specified character from string. syntax: TRIM(leading||trailing||both ‘char’ from ‘str’) example: SELECT TRIM(' My. SQL '); -> My. SQL SELECT TRIM(both ‘A’ from ‘ANISHA’) from dual; ->NISH

SELECT ASCII('dx'); -> 100 Character/String Functions • LPAD: - Return the string argument, left-padded with the specified character. syntax: LPAD(‘str 1’, length, ’char’) example: SELECT LPAD('hi', 4, '? ? ') from dual; -> '? ? hi' RPAD: - Return the string argument, right-padded with the specified character. syntax: RPAD(‘str 1’, length, ’char’) example: SELECT RPAD('hi', 5, '? ') from dual; -> 'hi? ? ? ‘ SELECT RPAD('hi', 1, '? ') from dual; -> 'h'

Numeric Functions • • • ABS POWER SQRT ROUND FLOOR CEIL TRUNC EXP(EXPONENT) GREATEST LEAST MOD

Numeric Functions • ABS: - Return the absolute value of n. syntax: ABS(n) example: SELECT ABS(-15) from dual; -> 15 • POWER: - Returns m raised to n power. ‘n’ must be an integer. syntax: POWER(m, n) example: SELECT POWER(3, 2) from dual; -> 9 SELECT POWER(3, 3) from dual; -> 27

Numeric Functions • SQRT: - Returns square root of ‘n’. syntax: SQRT(n) example: SELECT SQRT(9) from dual; -> 3 • ROUND: - Returns n, rounded to ‘m’ place to the right of decimal point. If ‘m’ is omitted, ‘n’ is rounded to 0 places (without float value). syntax: ROUND(m, [n]) example: SELECT ROUND(14. 55) from dual; -> 15 SELECT ROUND(14. 555, 2) from dual; -> 14. 56

Numeric Functions • FLOOR: - Returns the largest integer value that is less than or equal to a number. syntax: FLOOR(n) example: SELECT FLOOR(24. 8) “floor” from dual; -> 24 • CEIL: - Returns the integer value that is greater that or equal to a number. syntax: CEIL(n) example: SELECT CEIL(24. 8) from dual; -> 25

Numeric Functions • TRUNC: - Returns a number truncated to a certain number of decimal places. If decimal places is omitted, the function will truncate the number to 0 decimal places. syntax: TRUNC(number, [decimal places]) example: select trunc(15. 8556, 2) from dual; ->15. 85 • EXP: -Returns e raised to nth power, where e=2. 71828183. syntax: EXP(n) example: select EXP(3) from dual; ->20. 085536923

Numeric Functions • GREATEST: - Returns greatest value from list of expressions. syntax: GREATEST(expr 1, expr 2, . . expr 3) example: select greatest(5, 7, 8) from dual; -> 8 • LEAST: -Returns the least value from list of expression. syntax: LEAST(expr 1, expr 2, . . expr 3) example: select least(5, 7, 8) from dual; -> 5

Numeric Functions • MOD: - Returns remainder of 1 st number divided by second number passed as parameter. syntax: MOD(m, n) example: select mod(4, 2) from dual; ->0 select mod(5. 5, 2) from dual; ->1. 5

Date Functions To manipulate and extract values from date column oracle provides some date functions: SYSDATE is used to get system date. • ADD_MONTHS • LAST_DAY • MONTHS_BETWEEN • NEXT_DAY • ROUND • EXTRACT

Date Functions • ADD_MONTHS: - Returns the date after adding the number of months specified in function. syntax: ADD_MONTHS(date , no_of_month) example: select add_months(‘ 21 -jan-2014’, 4) from dual; -> 21 -MAY-14 • MONTHS_BETWEEN : -Returns the number of months between date 1 and date 2. syntax: MONTHS_BETWEEN(date 1, date 2) example: select months_between('15 -jan-2014', '15 -nov-2013') from dual; - >2

Date Functions • LAST_DAY: - Returns the last date of months specified in function. syntax: LAST_DAY(date) example: select last_day(sysdate) from dual; -> 31 -JAN-14 • NEXT_DAY: -NEXT_DAY returns the date of the first weekday named by char that is after date named by date. The return type is always DATE, regardless of the datatype of date. The argument char must be a day of the week syntax: NEXT_DAY(date, char) example: select next_day(sysdate, 'sunday') from dual; - >26 -jab-14

Date Functions • ROUND: - ROUND function returns a date rounded to a specific unit of measure. format parameter: Unit Valid format parameters Rounding Rule Year SYYYY, YEAR, SYEAR, YYY, Y Rounds up on July 1 st ISO Year IYYY, I Quarter Q Rounds up on the 16 th day of the second month of the quarter Month MONTH, MON, MM, RM Rounds up on the 16 th day of the month Week WW W W Day DDD, DD Minute MI Hour HH, HH 12, HH 24 Same day of the week as the first day of the year Same day of the week as the first day of the month

Date Functions syntax: ROUND( date, [ format ] ) example: ROUND(TO_DATE ('22 -AUG-03'), 'YEAR') would return '01 -JAN-04' ROUND(TO_DATE ('22 -AUG-03'), 'Q') would return '01 -OCT-03' ROUND(TO_DATE ('22 -AUG-03'), 'MONTH') would return '01 -SEP-03' ROUND(TO_DATE ('22 -AUG-03'), 'DDD') would return '22 -AUG-03‘ • EXTRACT: EXTRACT function extracts a value from a date or interval value. Syntax: EXTRACT ( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | TIMEZONE_ABBR } FROM { date_value | interval_value } )

Date Functions • Examples: SELECT EXTRACT(YEAR FROM DATE '2003 -08 -22') from dual ->2003 SELECT EXTRACT(MONTH FROM DATE '2003 -08 -22') from dual-> 8 SELECT EXTRACT(DAY FROM DATE '2003 -08 -22') from dual-> 22 SELECT EXTRACT(HOUR FROM TO_TIMESTAMP('01 -JAN-2005 19: 15: 26', 'DD-MON-YYYY HH 24: MI: SS')) AS HOUR FROM dual; ->19