Using single row functions to customize Tips:

1.   Difference between INSTR and SUBSTR ?

ð  INSTR is used to search a character.

SUBSTR is used to extract a part of string.

ð  INSTR can search for a string at different occurrences and can start its searching either from beginning or from end.

SUBSTR can extract the part of string which starts from beginning or from end.

ð  Syntaxes:

a.       INSTR( mainString, searchString, start, occurance)

b.      SUBSTR( mainString, start, length )

2.   Difference between REPLACE and TRANSLATE ?

ð  REPLACE does a String base replacement.

TRANSLATE does a Char base replacement.

ð  Syntax:

a.       REPLACE( mainString, searchString, replaceString )

b.      TRANSLATE( mainString, charString, translateString )

ð  Replace functions as below:

Main String:       “Hello1 Hello2 Hello3”

Search String: “Hello1”

Replace String: “Hello4”

New String:       “Bollo4 Hello2 Hello3”

Note:       It replaces only the respective String.

ð  Translate functions as below:

Main String:       “Hello1 Hello2 Hello3”

Search String: “Hello1”

Replace String: “Bollo4”

New String:       “Bollo4 Bollo2 Bollo 3”

Note:       It replaces all the characters where ever it appears in the String.

3.   MOD functionality

a.    MOD( 4,3) = 1.           4/3  remainder = 1

b.    MOD( 5,3) = 2.           5/3  remainder = 2

c.    MOD( 7,4) = 3.           7/4  remainder = 3

4.   Difference between ROUND and TRUNCATE ?

ð  Round and Truncate functions are used to find the apprx value.

ð  Round and Truncate functions can be applied on the Date’s also.

ð  Syntax:

a.    ROUND( decimal_number, number )
ROUND( date, mode )

b.    TRUNC( decimal_number, number )
TRUNC( date, mode )

ð  Rounding a Date will round it to CEIL value.

ð  Truncating a Date will truncate to FLOOR value.

                        More about Round & Trunc

5.   Adding Months to a date.

Select SYSDATE + 2*30 from dual;

     SYSDATE :    17/2/2012

     OUTPUT:      17/4/2012

Select ADD_MONTHS( sysdate, 2) from dual;

     SYSDATE :    17/2/2012

     OUTPUT:      17/4/2012

6.   Find the difference between 2 months.

Select (TO_DATE(’17-APR-2012’) - TO_DATE(’17-FEB-2012’))/30 from dual;

     OUTPUT:      2

Select MONTHS_BETWEEN(TO_DATE(’17-APR-2012’), TO_DATE(’17-FEB-2012’))

from dual;

     OUTPUT:      2

7.   Extract the Part of date.

a.    Get the year from the date.

o   Select Extract (YEAR from TO_DATE('01-JAN-2012')) from dual;

o   Select TO_CHAR (To_Date('01-JAN-2012'), ‘YYYY’) from dual;

b.    Get the month from the date.

o   Select Extract (MONTH from TO_DATE('01-JAN-2012')) from dual;

o   Select To_Char(TO_DATE('01-JAN-2012'), ‘MON’) from dual;

c.    Get the day from the date.

o   Select Extract (DAY from TO_DATE('01-JAN-2012')) from dual;

o   Select To_Char(TO_DATE('01-JAN-2012'), ‘dd’) from dual;

d.    Get the day from the date

o   Select To_Char(TO_DATE('01-JAN-2012'), ‘day’) from dual;

8.   Get the Last day, First Day of the month.

a.    Get the Last Date of Jan month.

o   Select LAST_DAY(TO_DATE('01-JAN-2012')) from dual;

o   returns ’31-JAN-2012’

b.    Get the Last Day of the Jan month.

o   Select TO_CHAR(LAST_DAY(TO_DATE('01-JAN-2012')),’day’) from dual;

o   returns “Tuesday”.

c.    Get the First Day of the Jan month.

o   select TO_CHAR(add_months(last_day('01-JAN-12')+1, -1),’day’) from dual;

o   returns “Sunday”.

9.   Get the Next required day from the current day.

a.    Get the Next Monday from current Day.

o   Select next_day( sysdate, ‘Sunday’) from dual;

b.    Get the last Sunday of the current Month.

o   select next_day(last_day(sysdate)-7,'sunday') from dual;

10.                Single row functions are the functions which are executed for each row processed by oracle.