SQL functions are built into Oracle and are available for use in various appropriate SQL statements.
Single-Row Functions:
--------------------
Single-row functions return a single result row for every row of a queried table or view.
These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses
---------------------------------------------------------
Number Functions
---------------------------------------------------------
Number functions accept numeric input and return numeric values.
Most of these functions return values that are accurate to 38 decimal digits
Eaxmple:
--------
Absolute-->select ABS(-125) from dual; --o/p:125
select sin(60) from dual;-- 0/p:-0.30481062110221670562564946547842584078
select Tan(60) from dual;-- 0/p: 0.3200403893795629749381876197057054331082
select cos(60) from dual;-- 0/p:-0.95241298041515629269381659599293329692
Round
-----
select round(125.12) from dual--o/p:125 --Returns a decimal number rounded of to a given decimal positions
select round(125.62) from dual--o/p:126
select round(125.62,0) from dual--o/p:126
select round(125.62,1) from dual--o/p:125.6
select round(125.62,2) from dual--o/p:125.62
select round(125.62,-1) from dual--o/p:130---It will round the left side of decimal value.
select round(154.62,-2) from dual--o/p:200
select round(14.62,-2) from dual--o/p:0
select round(1250.62,-3) from dual--o/p:1000
Trunc
-----
select trunc(1234.1234, 2), trunc(1234, 0), trunc(1234, -2) from dual
In first case values on right side of decimal point after 2nd place gets omited
In second case all values after decimal point omited
In third case two places till 2nd place prior to decimal point replacd with 0
select trunc(sysdate,'Q') from dual-->01-JAN-11
select trunc(sysdate,'I') from dual-->01-JAN-11
select trunc(sysdate,'Month') from dual-->01-MAR-11
select trunc(sysdate,'IW') from dual-->28-FEB-11
select trunc(sysdate,'D') from dual-->27-FEB-11
select trunc(sysdate,'DY') from dual-->27-FEB-11
select round(sysdate,'YEAR') from dual---Output->01-JAN-11
select round(sysdate,'Q') from dual--o/p:01-APR-11
select round(sysdate+15,'Month') from dual--o/p:01-APR-11
select round(sysdate,'Dy') from dual--o/p:06-MAR-11
select round(sysdate,'D') from dual--o/p:06-MAR-11
select round(sysdate,'iw') from dual--o/p:07-MAR-11
select round(sysdate,'DAY') from dual--o/p:06-MAR-11
Ceil
-----
Returns the lowest integer above the given number.
select ceil(3.456) “Ciel” from dual;O/p:4
floor
-----
Returns the highest integer below the given number.
select floor(3.456) “Floor” from dual; O/p: 3
MOD
---
Returns the remainder after dividing m with n.
Select mod(30,4) from dual;O/p: 2
POWER
-----
Returns the power of m, raised to n.
select power(2,3) from dual;--o/p: 8
examples:--SQRT,EXp
---------------------------------------------------------
Character Functions
---------------------------------------------------------
Upper,Lower,Initcap,Length,Rpad,Lpad,TRIM,Lrim,Rtrim,concat
Trim--->Trims a given character from left or right or both from a given string.
---
Select trim(0 from '00003443500') from dual; o/p:34435
Substring
---------
Returns a substring from a given string. Starting from position p to n characters.
select substr('Arunkumar',1,4) from dual;--o/p-->Arun
select substr('Arunkumar',-4,4) from dual;--o/p-->umar
select substr('Arunkumar',-1,4) from dual;--o/p-->r
select substr('Arunkumar',1,-4) from dual; --no rows returnd
Instring
--------
Tests whether a given character occurs in the given string or not.
If the character occurs in the string then returns the first position of its occurrence otherwise returns 0.
select instr('Arunkumar','a',1) from dual;--8
REPLACE
--------
Replaces a given set of characters in a string with another set of characters.
select replace('Arun Kumar Raju','Raju','Raja') from dual;
TRANSLATE
--------
This function is used to encrypt characters. For example you can use this
function to replace characters in a given string with your coded characters.
select
translate('interface',
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
'BCDEFGHIJKLMNOPQRSTUVWXYZAbcdefghijklmnopqrstuvwxyza') from dual;
SOUNDEX
-------
This function is used to check pronounciation rather than exact characters.
For example many people write names as “smith” or “smyth” or “smythe” but they are pronounced as smith only.
Select ename from emp where soundex(ename)=soundex('smith');
-------------------------------------------------------------------
Miscellaneous Single Row Functions
---------------------------------------------------------------------
NVL
---
This function is oftenly used to check null values. It returns expr2 if the expr1 is null, otherwise returns expr1
select nvl(sal,0) from emp;
NVL2
----
NVL2 returns expr2 if expr1 is not null, otherwise return expr3.
select salary,comm,nvl2(comm,salary+comm,salary) from emp;
NULLIF
------
Nullif compares expr1 with expr2. If they are equal then returns null, otherwise return expr1.
select nullif(1,2) from dual--1
select nullif(1,1) from dual--null
COALESCE
--------
Coalesce function returns the first not null value in the expression list.
select empno,ename,salary,comm,coalesce(salary+comm,salary,1000) “Net Sal” from emp;
ENAME SALARY COMM NET SAL
----- ------ ---- -------
SMITH 1000 500 1500
SAMI 3000 3000
SCOTT 1000
RAVI 500 1000
DECODE
-------
DECODE(expr, searchvalue1, result1,searchvalue2,result2,..., defaultvalue)
Decode functions compares an expr with search value one by one.
If the expr does not match any of the search value then returns the default value.
If the default value is omitted then returns null.
select decode(deptno,10,'Sales',
20,'Accounts',
30,'Production',
40,'R and D','Unknown Dept') As DeptName from emp;
Case
----
you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-ELSE statement.
The syntax for the case statement is:
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
Example:
-------
select case when sal>1000 then sal else comm end from scott.emp;
select supplier_id,
CASE
WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
from suppliers;
UID,User
--------
select UID,user from dual
--------------------------------------------------------------------------
Conversion Functions
--------------------------------------------------------------------------
CAST
----
the cast function converts one datatype to another
select cast( '03-Mar-2011' AS varchar2(30) ) from dual;
select cast( '03-Mar-2011' AS date ) from dual;
select cast( '12346' AS number ) from dual;
To_char
------
the to_char function converts a number or date to a string.
The syntax for the to_char function is:
to_char( value, [ format_mask ], [ nls_language ] )
select to_char(1210.73, '9999.9') from dual ---'1210.7'
select to_char(1210.73, '9,999.99') from dual-- '1,210.73'
select to_char(1210.73, '$9,999.00') from dual-- '$1,210.73'
select to_char(21, '000099') from dual-- '000021'
select to_char((sysdate),'fmD') from dual;--5--format mask day
select to_char((sysdate),'fmDay') from dual;--Thursday
select to_char(sysdate,'ddmmyyyy') from dual;
select to_char(sysdate,'mmyyyy') from dual;
To_NUmber
--------
In Oracle/PLSQL, the to_number function converts a string to a number.
select to_number('1210.73', '9999.99') from dual;
To_date
-------
In Oracle/PLSQL, the to_date function converts a string to a date.
select to_date('2011/03/01', 'yyyy/mm/dd') from dual;
select to_date('070903', 'MMDDYY') from dual;--09-JUL-03
To_timestamp
------------
In Oracle/PLSQL, the to_timestamp function converts a string to a timestamp.
select to_timestamp('2011/03/03 12:13:18', 'YYYY/MM/DD HH:MI:SS') from dual
Convert number to words
----------------------
select to_char(to_date(100,'J'),'JSP') from dual;
--------------------------------------------------------------------------
Date Functions
--------------------------------------------------------------------------
select sysdate,current_date from dual;
select sysdate,systimestamp from dual;
select last_day(sysdate) from dual;
select next_day(current_date,'Mon') from dual
Add_months
---------
the add_months function returns a date plus n months
select add_months(sysdate,-3) from dual;
select add_months(sysdate,3) from dual;
Months_between
--------------
the months_between function returns the number of months between date1 and date2.
syntax:months_between( date1, date2 )
select add_months(sysdate,-3) from dual;
select months_between(sysdate+90,sysdate) from dual.
Ur's
AmarAlam
0 comments:
Post a Comment