Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Pre-define Functions in Oracle – Part XVII

Posted by Ravi Varma Thumati on October 21, 2009

Oracle functions serve the purpose of manipulating data items and returning a result. Functions are also capable of accepting user-supplied variables or constants and operations on them. Such variables and constants are called arguments.

Functions are classified into Group Functions and Single Row Functions (Scalar Functions).

Before we check single row function and group function, we will take a look on “Dual table”

The Oracle Table “Dual”

Dual is a small oracle worktable, which consists of only one row and one column, and contains the value x in that column. Besides arithmetic calculations, it also supports date retrieval and it’s formatting.

SQL> select 2*2 from dual;

2*2

4

Single Row Functions (Scalar Functions):

Functions that act on only one value at a time are called as Single Row Functions. A Single Row function returns one result for every row of a queried table or view.

Single Row functions can be further grouped together by the data type of their arguments and return values. Functions can be classified corresponding to different data types as:

  • String Functions : Work for String Data type
  • Numeric Functions : Work for number Data type
  • Conversion Functions : Work for conversion of one data type to another
  • Date Functions : Work for Date Data type

String Functions:

String functions accept string input and return either string or number values.

1) Initcap (Initial Capital): This String function is used to capitalize first character of the input string.

Syntax:

initcap(string)

Example:

SQL> select initcap(‘azure’) from dual;

INITC

——-

Azure

2) Lower: This String function will convert input string in to lower case.

Syntax:

Lower(string)

Example:

SQL> select lower(‘AZURE’) from dual;

LOWER

——-

azure

3) Upper: This string function will convert input string in to upper case.

Syntax:

Upper(string)

Example:

SQL> select upper(‘azure’) from dual;

UPPER

——-

AZURE

4) Ltrim (Left Trim): Ltrim function accepts two string parameters; it will fetch only those set of characters from the first string from the left side of the first string, and displays only those characters which are not present in second string. If same set of characters are not found in first string it will display whole string

Syntax:

Ltrim(string,set)

Example:

SQL>select ltrim(‘azuretech’,’azure’) from dual;

LTRI

——

tech

5) Rtrim (Right Trim): Rtrim function accepts two string parameters; it will fetch only those characters from the first string, which is present in set of characters in second string from the right side of the first string.

Syntax:

Rtrim(string,set)

Example:

SQL>select rtrim(‘azuretrim’,’trim’) from dual;

RTRIM

——-

azure

6) Translate: This function is useful when you want to encrypt string. It will take first character from string1 and search the same character in string2 if that character is found than it replaces that character out of string3 on base of position of character found in string2. In below given example first character “a” of string1 is found at position no 2 in string2, so it will extract second character from string3. Same way second character “b” is found at position number 4 in string2, so it will extract fourth character from string3 and so on. If any character in string1 is not found in string2 then it is kept unchanged.

Syntax:

Translate(string1, string2, string3)

Example:

SQL>select translate(‘abcde’,’xaybzcxdye’,’tanzmulrye’) from dual;

TRANS

——-

azure

7) Replace: This function is useful when you want to search a specified string and replace it with particular string form the string provided. For example, you want to search ‘A’ from the ‘TACHNOLOGIAS’ and replace it with ‘E’ to make it ‘TECHNOLOGIES’. Replace function accepts three arguments first argument is, from which string you want to search, second argument is what you want to search from the first argument and third argument is replace string, value of second argument, if found will be replaced with value passed in third argument.

Syntax:

Replace(string, searchstring, replacestring)

Example:

SQL> select replace(‘jack and jue’,’j’,’bl’) from dual;

REPLACE(‘JACKA

———————

black and blue

8) Substr: Substring fetches out a piece of the string beginning at start and going for count characters, if count is not specified, the string is fetched from start and goes till end of the string.

Syntax:

Substr(string, starts [, count])

Example:

SQL>select substr(‘azuretechnology’,4,6) from dual;

SUBSTR

——

retech

9) Chr: Character function except character input and returns either character or number values. The first among character function is chr. This returns the character value of given number within braces.

Syntax:

Chr(number)

Example:

SQL>select chr(65) from dual;

C

A

10) Lpad (Left Pad): This function takes three arguments. The first argument is character string, which has to be displayed with the left padding. Second is a number, which indicates total length of return value and third is the string with which left padding has to be done when required.

Syntax:

Lpad(String,length,pattern)

Example:

Sql > select lpad(‘Welcome’,15,’*’) from dual;

LPAD(‘WELCOME’,

———————-

********Welcome

11) Rpad (Right Pad): Rpad does exact opposite then Lpad function.

Syntax:

Lpad(String,length,pattern)

Example:

SQL> select rpad(‘Welcome’,15,’*’) from dual;

RPAD(‘WELCOME’,

———————-

Welcome********

12) Length: When the length function is used in a query. It returns length of the input string.

Syntax:

Length(string)

Example:

SQL>select length(‘auzre’) from dual;

LENGTH(‘AUZRE’)

———————-

5

13) Decode: Unlike the translate function which performs character-by-character replacement the decode function does a value-by-value replacement.

Syntax:

Select decode(column name,if,then,if,then…….) from <tablename>;

Example:

SQL> select deptno,decode(deptno,10, ‘Sales’, 20, ‘Purchase’, ‘Account’)

DNAME from emp_master;

DEPTNO DNAME

——— ————

10 Sales

20 Purchase

20 Purchase

10 Sales

4 rows selected.

14) Concatenation ( || ) Operator: This operator is used to merge two or more strings.

Syntax:

Concat(string1,string2)

SQL> select concat(‘Azure’,’ Technology’) from dual;

CONCAT(‘AZURE’,’

————————

Azure Technology

SQL> select ‘ename is ‘||ename from emp_master;

‘ENAME IS’||ENAME

————————-

ename is Allen

ename is King

ename is Martin

ename is Tanmay

4 rows selected.

Numeric Functions:

1) Abs (Absolute): Abs() function always returns positive number.

Syntax:

Abs(Negetive Number)

Example:

SQL> select Abs(-10) from dual;

ABS(-10)

————

10

2) Ceil: This function will return ceiling value of input number. i.e. if you enter 20.10 it will return 21 and if you enter 20.95 then also it will return 21. so if there is any decimal value it will add value by one and remove decimal value.

Syntax:

Ceil (Number)

Example:

SQL>select Ceil (23.77) from dual;

CEIL(23.77)

—————-

24

3) Floor: This function does exactely opposite of the ceil function.

Syntax:

Floor(Number)

Example:

SQL>select Floor(45.3) from dual;

FLOOR(45.3)

—————-

45

4) Power: This function will return power of raise value of given number.

Syntax:

Power(Number, Raise)

Example:

SQL>Select power (5,2) from dual;

POWER(5,2)

—————

25

5) Mod: The function gives the remainder of a value divided by another value.

Syntax:

Mod(Number, DivisionValue)

Example:

SQL>select Mod(10,3) from dual;

MOD(10,3)

————-

1

6) Sign: The sign function gives the sign of a value without its magnitude.

SQL>select sign(-45) from dual;

SIGN(-45)

————-

-1

SQL>Select sign(45) from dual;

SIGN(45)

————

1

Date Function:

1) Add_Months: The add_months data function returns a date after adding a specified data with the specified number of months. The format is add_months(d,n), where d is the date and n represents the number of months.

Syntax:

Add_Months(Date,no.of Months)

Example:

SQL> select Add_Months(sysdate,2) from dual;

This will add two months in system date.

ADD_MONTH

————

02-NOV-01

2) Last_day: Returns the last date of month specified with the function.

Syntax:

Last_day(Date)

Example:

SQL> select sysdate, last_day(sysdate) from dual;

SYSDATE LAST_DAY

———— ————-

02-SEP-01 30-SEP-01

3) Months_Between: Where Date1, Date2 are dates. The output will be a number. If Date1 is later than Date2, result is positive; if earlier, negative. If Date1 and Date2 are either the same days of the month or both last days of months, the result is always an integer; otherwise Oracle calculates the fractional portion of the result based on a 31-day month and considers the difference in time components of Date1 and Date2.

Syntax:

Months_Between(Date1,Date2)

Example:

SQL>select months_between(sysdate,’02-AUG-01’) “Months” from dual;

MONTHS

———

4

4) Next_Day: Returns the date of the first weekday named by ‘char’ that is after the date named by ‘Date’. ‘Day’ must be the day of the week.

Syntax:

Next_Day(Date,Day)

Example:

SQL>select next_day(sydate, ‘sunday’) “Next” from dual;

This will return date of next sunday.

NEXT_DAY

————-

09-SEP-00

5) Round: This function returns the date, which is rounded to the unit specified by the format.

Syntax:

Round (Date, [fmt])

If format is not specified by default date will be rounded to the nearest day.

Example:

SQL>Select round(‘4-sep-01’,’day’) “Rounded” from dual;

Rounded

————-

02-SEP-01

The date formats are ‘month’ and ‘year’.

If rounded with ‘month’ format it will round with nearest month.

If rounded with ‘year’ format it will round with nearest year.

6) Trunc (Truncate): This function returns the date, which is truncated to the unit specified by the format.

Syntax:

Trunc(Date,[fmt])

If format is not specified by default date will be truncated.

Example:

This will display first day of current week.

SQL>Select Trunc(‘4-sep-01’,’day’) “Truncated” from dual;

Truncated

————-

02-SEP-01

The date formats are ‘month’ and ‘year’.

If rounded with ‘month’ format it will display first day of the current month.

If rounded with ‘year’ format it will display first day of the current year.

Conversion Functions:

Conversion functions convert a value from one data type to another. The conversion functions are classified into the following:

  • To_Number()
  • To_Char()
  • To_Date()

1) To_Number: The to_number function allows the conversion of string containing numbers into the number data type on which arithmetic operations can be performed.

Example:

SQL>Select to_number(‘50’) from dual;

TO_NUMBER(‘50’)

———————-

50

2) To_Char: To_char function converts a value of number data type to a value of char data type, using the optional format string. It accepts a number (no) and a numeric format (fmt) in which the number has to appear. If ‘fmt’ is omitted, ‘no’ is converted to a char exactly long enough to hold significant digits.

Syntax:

To_char(no,[fmt])

Example:

SQL> select to_char(17145,’$099,999’) “Char” from dual;

Char

————

$017,145

To_char converts a value of date datatype to character value. It accpets a date, as well as the format (fmt) in which the date has to appear. ‘fmt’ must be the date format. If ‘fmt’ is omitted, ‘date’ is converted to a character value in the default date format “dd-mon-yy”.

Syntax:

To_char(Date,[fmt])

Example:

SQL>select to_char(hiredate, ‘month dd yyyy’) “HireDate” from emp_master

where salary = 10000;

HireDate

————————-

January 01 2000

September 16 2000

3) To_Date: The format is to_date(char [,fmt]). This converts char or varchar datatype to date datatype. Format model, fmt specifies the form of character. Consider the following example which returns date for the string ‘January 27 2000’.

Syntax:

To_date(char,[fmt])

Example:

SQL>select to_date(’27 January 2000’,’dd/mon/yy’) “Date” from dual;

Date

————-

27-JAN-00

Miscellaneous Functions:

The following are some of the miscellaneous functions supported by Oracle.

  • Uid
  • User
  • Nvl
  • Vsize

1) Uid (User Id): This function returns the integer values corresponding to the user currently logged in. The following example is illustrative.

Example:

SQL> select uid from user;

UID

—-

320

2) User: This function returns the login’s user name, which is in varchar2 datatype. Consider the following example.

Example:

SQL> select user from dual;

USER

——-

Scott

3) Nvl (Null Value): The nvl function is used in cases where we want to consider Null values as another value.

Syntax:

Nvl(expression1, expression2)

If the expression1 is null then nvl will return expression2, and if expression1 is not null then it will return

expression1.

Example:

SQL> Insert into emp_master (empno,ename,salary) values

(1125,’Ward’,null);

SQL>select nvl(salary,0) from emp_master;

SALARY

———

10000

3400

7000

10000

0

5 rows selected.

  • Null values and zeroes are not equivalent. Null values are represented by blank and zeroes are represented by (0).

4) Vsize function: This function returns no. of bytes in the expression. If expression is Null, it returns Null.

Syntax:

Vsize(expression)

Example:

SQL> select vsize(‘azure’) from dual;

VSIZE

——

5

Group Functions:

A group functions returns a result based on a group of rows. Some of these are just purely mathematical functions. The group functions supported by Oracle are summarized below:

1) Avg (Average): This function will return the average of values of the column specified in the argument of the column.

Example:

SQL> select avg(comm) from emp_master;

AVG(COMM)

————-

766.66667

2) Min (Minimum): The function will give the least of all values of the column present in the argument.

Example:

SQL>Select min(salary) from emp_master;

MIN(SALARY)

—————-

3400

3) Max (Maximum): To perform an operation, which gives the maximum of a set of values the max, function can be made use of.

Example:

SQL>select max (salary) from emp_master;

This query will return the maximum value of the column specified as the argument.

MAX (SALARY)

—————-

10000

4) Sum: The sum function can be used to obtain the sum of a range of values of a record set.

Example:

SQL>Select sum(comm) from emp_master;

SUM(COMM)

————-

2300

5) Count: This function is used to count number rows. It can take three different arguments, which mentioned below.

Syntax:

Count(*)

Count(column name)

Count(distinct column name)

Count (*): This will count all the rows, including duplicates and nulls.

Example:

SQL>Select count(*) from emp_master;

COUNT(*)

————

4

Count (Column name) : It counts the number of values present in the column without including nulls.

Example:

SQL> select count(comm) from emp_master;

COUNT(comm)

—————-

3

Count (distinct column name) : It is similar to count(column name) but eliminates duplicate values while

counting.

Example:

SQL>Select count(distinct deptno) from emp_master;

COUNT(DEPTNO)

——————-

2

Group By Clause

Group by clause is used with group functions only. Normally group functions returns only one row. But group by clause will group on that column.

The group by clause tells Oracle to group rows based on distinct values for specified columns, i.e. it creates a data set, containing several sets of records grouped together based on a condition. Select group function from table name group by column name

Example:

SQL>select deptno,count(*) from emp_master group by deptno;

DEPTNO COUNT(*)

——— ————

10 2

20 2

1

Having Clause

The having clause is used to satisfy certain conditions on rows, retrieved by using group by clause. Having clause should be proceeding by a group by clause. Having clause further filters the rows return by group by clause.

Example

SQL> select deptno,count(*) from emp_master group by deptno having

Deptno is not null;

DEPTNO COUNT(*)

——— ————

10        2

20        2

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: