1 / 26

SQL Functions

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. Functions. Multiple-row

fperkins
Download Presentation

SQL Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Functions

  2. 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.

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

  4. 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

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

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

  7. 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

  8. 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;

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

  10. SELECT ASCII('dx'); -> 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(5myNumber123', ‘512‘,’789’); ->7myNumber893 • CONCAT: -Returns the string that results from concatenating the arguments. syntax: CONCAT(str1,str2) example: SELECT CONCAT(‘My’,’Sql’); -> MySql

  11. SELECT ASCII('dx'); -> 100 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(' MySQL');-> MySQL SELECT LTRIM(‘ANISHA’,’A’) from dual; ->NISHA

  12. SELECT ASCII('dx'); -> 100 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('MySQL ');-> MySQL 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(' MySQL ');-> MySQL SELECT TRIM(both ‘A’ from ‘ANISHA’) from dual; ->NISH

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

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

  15. 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

  16. 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

  17. 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

  18. 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

  19. Numeric Functions • GREATEST: - Returns greatest value from list of expressions. syntax: GREATEST(expr1,expr2,..expr3) example: select greatest(5,7,8) from dual; -> 8 • LEAST: -Returns the least value from list of expression. syntax: LEAST(expr1,expr2,..expr3) example: select least(5,7,8) from dual; -> 5

  20. Numeric Functions • MOD: - Returns remainder of 1st 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

  21. 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

  22. 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 date1 and date2. syntax: MONTHS_BETWEEN(date1,date2) example: select months_between('15-jan-2014','15-nov-2013') from dual;- >2

  23. 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

  24. Date Functions • ROUND: - ROUND function returns a date rounded to a specific unit of measure. format parameter:

  25. 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 } )

  26. 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 HH24:MI:SS')) AS HOUR FROM dual; ->19

More Related