SQL Functions Part3 (Date Functions)

We have many date functions available in SQL which are very useful while working with data of date type in SQL. Date functions will accept date data as input and will produce either date or numerical output depending on the function.

We have many date functions available in SQL. We can do arithmetic operations also on dates. So, First I will discuss about the arithmetic operations which we can do on date data. Before discussing about this we need to know about SYSDATE function.

SYSDATE function :


   SYSDATE is a function which will always give the current date according to the database system. So,if we want to use current date in any of the SQL queries then we can use SYSDATE function. This is the only function which will not contain any arguments.

 SELECT sysdate FROM dual;

This query will give the current date according to database system.

Arithmetic Operations On Date Data ::


   We can add a number to date by using + operator. If we add any number to date, then database will provide the result by adding those many number of days to that date.

Ex : SELECT sysdate +  4 From dual;

 This query will give the result by adding 4 days to sysdate . For ex , if it is 13-nov-2011 then we will get result as 17-nov-2011.

    We can subtract a number from a date by using - operator. If we subtract any number from date, then database will provide the result by subtracting those many number of days from that date.

Ex : SELECT sysdate -  4 From dual;

 This query will give the result by subtracting 4 days from sysdate . For ex , if it is 13-nov-2011 then we will get result as 09-nov-2011.

   We can subtract two dates also using - operator. This will give us the difference between those two dates in terms of number of days. So, if we want to find date difference in terms of number of days then we have to use - operator.

 Ex : SELECT to_date(17-jun-2011)-to_date(14-jun-2011) from dual;

  This will give us the result  as 3. Here I have used to_date function because, generally arithmetic operators will expect numeric values. So, even if we provide date values in valid format it will treat it as number and will throw invalid number exception. To avoid it, we have to convert these two dates using to_date function. So, that it will consider these two values as dates. I will discuss about this to_date function in detail in my coming posts.

So, this is about the arithmetic operations which we can do on date data.

Date Functions ::

  We have many date functions available in SQL. I will explain about some important date functions in this post.

ROUND() Function :


   Round function is used to rounding off  the date values. We can round the date values depending on either month or year . For this function input is date and output is also a date.

Syntax : Round ( date , 'MONTH' or 'YEAR')

  In this syntax,

   ---->  we have to give second argument as either 'MONTH' or 'YEAR' depending on with respect to
             what we want to round the date.

    The logic for rounding of with month is,

   * if date in input date is <15 then we will get first of current month in input as output.
   * if date in input date is >=15 then we will get first of next month  as output.

  Ex : SELECT ROUND(to_date('14-jul-2011'),'MONTH') FROM dual;

   For this query we will get output as , '01-jul-2011' as day<15.

    SELECT ROUND(to_date('16-jul-2011'),'MONTH') FROM dual;

   For this query we will get output as , '01-aug-2011' as day>=15.

    The logic for rounding of with year is,

   * if month in input date is <6 then we will get first of current year in input as output.
   * if month in input date is >=6 then we will get first of next year  as output.

   Ex : SELECT ROUND(to_date('14-mar-2011'),'YEAR') FROM dual;

   For this query we will get output as , '01-jan-2011' as month<6.

    SELECT ROUND(to_date('16-jul-2011'),'YEAR') FROM dual;

   For this query we will get output as , '01-jan-2012' as month>=6.

TRUNC() Function :


   Trunc function is used to truncate  the date values. We can truncate the date values depending on either month or year . For this function input is date and output is also a date.

Syntax : Trunc ( date , 'MONTH' or 'YEAR')

  In this syntax,

   ---->  we have to give second argument as either 'MONTH' or 'YEAR' depending on with respect to
             what we want to truncate the date.

    The logic for truncating of with month is,

   * irrespective of date in input date, we will get first of current month in input as output.

  Ex : SELECT TRUNC(to_date('14-jul-2011'),'MONTH') FROM dual;

   For this query we will get output as , '01-jul-2011'.

    SELECT TRUNC(to_date('16-jul-2011'),'MONTH') FROM dual;

   For this query we will get output as , '01-jul-2011' .

    The logic for rounding of with year is,

   *  irrespective of month in input date, we will get first of current year in input as output.

   Ex : SELECT TRUNC(to_date('14-mar-2011'),'YEAR') FROM dual;

   For this query we will get output as , '01-jan-2011'.

    SELECT TRUNC(to_date('16-jul-2011'),'YEAR') FROM dual;

   For this query we will get output as , '01-jan-2011'.     


  MONTHS_BETWEEN() Function :


      Months_Between() function is used to find difference between two dates in terms of number of months. For this function input is dates and the output is number.

 Syntax : MONTHS_BETWEEN ( date1, date2 )

 Ex : SELECT MONTHS_BETWEEN ( to_date('14-jul-2011'), to_date('21-sep-2011')) FROM dual;

  For this query we will get output as -2.2258065. We got negative value because we gave least value as the  first  input.

  ADD_MONTHS() Function :

      Add_Months() function is used to add certain number of months to the input date.  For this function input is date and output is also a date.

 Syntax : ADD_MONTHS(date, number_of_days)

  In this syntax ,

   number_of_days is the number which will decide number of months to be added to input date.

 Ex : SELECT ADD_MONTHS (to_date('12-jun-2011'), 4) FROM dual;

 This will give output as '12-oct-2011' by adding 4 months to input date.

  NEXT_DAY() Function :


    Next_day() function is used to find the next day as specified in the syntax. For this function input is a date and output is also a date.

  Syntax : NEXT_DAY(date,'DAY')

  In this syntax,

     --> 'DAY' refers to the day which we want after the specified date. List of values for this day are,

           'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT, 'SUN'.

      This means that, if we give day as 'THU' in the syntax, then the database will provide the output as the date on which next Thursday will come from the input date.

  Ex : SELECT NEXT_DAY(sysdate, 'FRI') FROM dual;

  Let us say here sysdate is '13-nov-2011' which is Sunday. So, we will get the output for the above query as '18-nov-2011' . This is the date on which we will get immediate next friday after the date '13-nov-2011'.


  LAST_DAY() Function :


     Last_Day() function is used to find the date of last day of the month specified in the input date. For this function input is a date and output is also a date.

   Syntax :  LAST_DAY (date);

  Ex : SELECT LAST_DAY (to_date('15-jul-2011')) From dual;

  This will give us the output as '31-jul-2011' as it is the last day of july month which is specified in the input
  date.

 These are the some of the important date functions which are available in SQL.

  

SQL Functions Part2 (Character Functions)

We have many character functions available in SQL which are very useful while working with the SQL statements. In this post I will discuss about those functions.

As the name it self specifying, character functions will work on character data . Character functions will accept character data as input and will produce output as either character or numeric data based on the type of function we are using.

Basically character functions are divided into two types.

    1. Case manipulation Functions
    2. Character manipulation Functions

Case manipulation Functions ::

   Case manipulation functions will work on the case of the characters. These are used for case conversions.

We have three functions available under this category.

   a) UPPER()
   b) LOWER()
   c) INITCAP()

UPPER() :

   This function will convert all the characters of input string to upper case letters. For this function input is a character and output is also a character.

 Syntax :  UPPER(i/p string)

 Ex : SELECT UPPER('Ram Babu') FROM dual;

 This will give result as 'RAM BABU'.

 LOWER() :

    This function will convert all the characters of input string to lower case letters. For this function input is a character and output is also a character.

 Syntax :  LOWER(i/p string)

 Ex : SELECT LOWER('Ram Babu') FROM dual;

 This will give result as 'ram babu'.

 INITCAP() :

     This function will convert first character of input string to upper case letter and remaining characters to lower case letters. For this function input is a character and output is also a character.

 Syntax :  INITCAP(i/p string)

 Ex : SELECT INITCAP('rAM Babu') FROM dual;

 This will give result as 'Ram babu'.   
      
  These are the three case manipulation functions available in SQL. These are very useful in writing SELECT statements. For example let us say we want to find out all the employees from employee table whose name is 'Abc'. As I already discussed in my previous posts character comparison is case sensitive. We don't know how the names were stored in the employee table. the same name Abc can be stored in many ways like ABC, abc, Abc. But we want all those results. In this case case manipulation functions will help us.

Query for this scenario can be written as,

  SELECT * FROM Emp WHERE upper(ename) = 'ABC';

 In this query I am converting the ename to upeer case letters and comparing it with 'ABC'.So, in this query irrespective of case of names in the table we will get all the employees with name abc using the above query.

So, like this we can work with case manipulation functions.

Character manipulation Functions ::

   Character manipulation functions will accept character inputs and will produce either character or numeric outputs. We have many character manipulation functions available in SQL. I will discuss important character manipulation functions in this post.

CONCAT() Function :

   This function is used to combine two strings into a single string. For this function input is a character string and output is also a character string. Using this function we can combine only two strings at a time.

  Syntax :  CONCAT ( str1, str2 )

 Ex : SELECT CONCAT ('Ram','Babu') FROM dual;

 This will give the output as 'RamBabu'.

 If I want to combine more than two strings then I have to go for nesting of functions.

 Nesting of functions ::

   If we call a function inside another function, then it will be called as nesting of functions. In this we will give output of one function as input to other function.

So, in case of CONCAT() function if I want to combine more than two strings then I have to go for nesting.

  CONCAT(str1,CONCAT(str2,str3))

Ex : SELECT CONCAT ('Kandimalla',CONCAT('Ram','Babu')) FROM dual;

  So, this will give output as KandimallaRamBabu. Here I gave output of the one CONCAT function as input to another CONCAT function. Like this I can do nesting of the functions.

SUBSTR() Function :

  This function is used to extract some part of input string. This function will accept character input and will produce character output. Syntax for this is,

 Syntax : SUBSTR(i/pstring , starting_pt_of_extraction, o/pstring_length)

 Here in this syntax,

  --> starting_pt_of_extraction is a number which will specify the position in input string from which the
        extraction should be started.
  --> o/pstring length will decide number of characters to be there in output string.

 Ex : SELECT SUBSTR('Hello World', 3, 5) From dual;

  Here we gave second argument as 3. It means extraction should start from 3rd position. So, it wil start from letter 'l'. we gave 3rd argument as 5. It means we need 5 characters to be present in output. So, we
will get output as,

    'llo W'.

This is how substring function will work.

 * If we exclude 3rd argument in the syntax, then by default total string from the starting point of extraction
    will be extracted.

   Ex : SELECT SUBSTR('Hello World',3) FROM dual;

  This will give output as,  'llo World'.

 We can extract the strings from ending also using SUBSTR function. For this we just need to give two arguments. One is input string and other one is number of characters to be there in output string (-ve value).

 Ex : SELECT SUBSTR('Hello World',-4) FROM dual;

 This will give output as, 'orld'.

 INSTR() Function :

     Instring function is used to find the position of a string in the input string. For this function input is a character string but the output is a number. (As we are finding position).

 Syntax :  INSTR (i/pstring, search_string, starting_pt_of_search, occurance)

  In this syntax,

  --> i/pstring refers to the Input string
  --> search_string refers to the string which we want to search in the input string
  --> starting_pt_of_search is a number which refers to the position from which we want to start searching
  --> occurance refers to which occurance of the string we want.

  This function will always give us the position of the string by calculating it from first position only irrespective of the starting_pt_of_search. This starting_pt_of_search is used for deciding occurance of the string. Let me explain this with an example.

 Let us say we have string 'Hello World'. In this string I want to search for string 'l'. But we have three 'l' s in the input string. So, which 'l' position we want ???. This will be decided by the occurance. So, if I say occurance as 1 it means I want position of first 'l'. If it is 2 then I want position of 2nd 'l'.
Again this occurance will be decided by starting_pt_of_search. If I start searching from 4th position then I will be having two occurances of 'l' only as one 'l' is in 3rd position. In any case final position of the string will be calculated from first position only.

 Ex : SELECT INSTR('Hello World','l',4,3) From dual;

  This will give result as 0. Because from 4th position we don't have 3rd occurance for 'l'.

    SELECT INSTR('Hello World','l',3,3) From dual;

  This will give result as 10. As third 'l' from position 3 is at 10th position in whole string.

    SELECT INSTR('Hello World','l',4,1) From dual;

  This will give result as 4. As 1st 'l' from position 4 is at 4th position in whole string.

If we exclude 4th argument in the syntax then by default it will give the position of first occurance of the string
specified by starting_pt_of_search.

   SELECT INSTR('Hello World','l',2) From dual;

  This will give us result as 3. As 1st 'l' from position 2 is at 3rd position in whole string.

If we exclude both 2nd and 3rd arguments in the syntax then by default we will get 1st occurance of the string from 1st position.

  SELECT INSTR('Hello World','l') From dual;

  This will give us result as 3. As 1st 'l' from position 1 is at 3rd position in whole string.

 This is how we can find the position of a string in the input string using INSTR() Function.

Length() Function :

    Length function is used to find the length of the input string in number of characters. For this function input is a character string and output is a number.

Syntax : Length(i/pstring)

 Ex : SELECT LENGTH('Hello World') FROM dual;

 This will give us the output as 11 as space is also a character.

LPAD() and RPAD() Functions :

   These functions as used to pad a character or set of characters to either left (LPAD) or right (RPAD) of the input string. This is similar to concatenation. But here we will concatenate same string number of times on either left or right side depending on the output length specified.

Syntax : LPAD(i/pstring, length_of_o/p_string, padding_string)
               RPAD(i/pstring, length_of_o/p_string, padding_string)

 Here in this syntax,

 --> length_of_o/p_string is a number which will specify the total length of output string we want after
       padding.
 --> padding_string will specify the string to be padded.

 Ex : SELECT LPAD('Ramu', 9 , '*') From dual;

  This will give output as, '*****Ramu' as we have mentioned total output string length as 9, we will get 5 stars padded to the input string on left side.

    SELECT RPAD('Ramu', 9 , '*') From dual;

  This will give us the output as, 'Ramu*****'.

 If we want to pad the same string on both sides of the input string then we have to use nesting of the functions concept. So, here first we have to use LPAD or RPAD and then we have to give output of these as input to RPAD or LPAD respectively to get desired result. This can be written as shown below.

  SELECT RPAD(LPAD('Ram',9,'*'),14,'*') From dual;

  This will give us the output as, '*****Ramu*****'.

If we have not specified the sufficient output string length to accomadate all the character of padding string then some part of string will be padded depending on the length.

  SELECT LPAD('Hello',7,'abcd') From dual;

  For this we will get output as 'abHello'. Even though we have 4 characters in the padding string, only two characters were padded to input string because of the output length we have specified.

TRIM(), LTRIM() and RTRIM() Functions :

   Trim() Function is used to trim heading and trailing characters from the input string.

 Syntax : TRIM(Leading or Trailing or both, trim_character from i/pstring)

   In this syntax,

   --> if we gave leading then character in first position will be compared for removal. If we gave trailing
         then character in last position will be compared for removal. if we gave 'both' then both first and
         last characters will be compared for removal.

   Ex : SELECT TRIM(Leading 'H' from 'Hello WorldH') From dual;

    This will give us output as 'ello WorldH'.

    SELECT TRIM(Trailing 'H' from 'Hello WorldH') From dual;

    This will give us output as 'Hello World'.

    SELECT TRIM('H' from 'Hello WorldH') From dual;

    This will give us output as 'ello World'. Here no need of specifying 'both' keyword as it is default.

  This TRIM() function can be used to remove heading and trailing spaces from the input string.

  Syntax : TRIM(i/pstring)

   So, if we give like this then both heading and trailing spaces if any will be removed from input string.

  Ex : SELECT TRIM('   Hello World    ') From dual;

   This will give output as 'Hello World'.

 LTRIM() function is used to remove all the spaces which are at the starting of the input string. (left side).

 Syntax : LTRIM(i/pstring)

  Ex : SELECT LTRIM('   Hello World    ') From dual;

   This will give output as 'Hello World    '.

 RTRIM() function is used to remove all the spaces which are at the ending of the input string. (right side).

 Syntax : RTRIM(i/pstring)

  Ex : SELECT RTRIM('   Hello World    ') From dual;

   This will give output as '    Hello World'.

REPLACE() Function :

  Replace function is used to replace some part of the input string with other string.

 Syntax : REPLACE(i/p string, search_string, replacement_string)

  Here in this syntax,

    ---> search_sting refers to the string which we want to replace in the input string
    ---> replacement_string refres to the replacement string for the search_string

  Ex : SELECT REPLACE('Ram Babu','Ram','Mahesh') From dual;

  This will give us the output as 'Mahesh Babu'. This is how we can use REPLACE function.

So, this is about the some of the important character functions available in SQL.

Note :Here in this post I have covered all the important character functions. If any information is required on any other character functions, post in comments. I will provide the required explanation.

SQL Functions Part 1 (Number Functions).

We have many built in functions available in SQL which are very useful while working with the SELECT statements. Depending on how they are working and how they are producing the results, SQL functions are divided into two types.

    1. Single row Functions
    2. Multiple row Functions.

In this post I want to discuss about some of the single row functions available in SQL.

Single row Functions ::


     The functions which will work on each row of a table and which will produce a result per each row are called as Single row functions. These functions will produce a result for each row they are effecting.

We have six types of Single row Functions available in SQL.

   1. Number Functions
   2. Character Functions
   3. Date Functions
   4. Conversion Functions
   5. General Functions
   6. Conditional Functions

In this post I want to discuss about Number functions. I will discuss about remaining functions in my coming posts.

1. Number Functions ::


  The functions which will work on numeric data are called as Number Functions. These functions will accept numeric input and will produce the numeric output.

 There are three numeric functions available in SQL.

 a) Round()
 b) Trunc()
 c) Mod()

Round() Function :


   This function is used to round the column or value or result of an expression to specified decimal places.
This will follow the mathematical rounding of operation rules.

  Ex : if we are rounding 423.456734 to 2 decimal places then result will be 423.46. (In rounding of operation 1 will be added to preceding number if current number is >=5).

 Syntax :: Round(input number, precision)

  Here precision will decide the number of decimal places to be present in the output.

 Ex : SELECT Round(2345.76234,2) FROM dual;

  This query will give result as 2345.76.

 If we exclude the precision in the syntax, then by default database will take it as 0.

 Ex : SELECT Round(2345.76234) FROM dual;

 This query will give result as 2346.

 If we give precision as negative value then the numbers to the left of decimal point will be rounded.

  Ex : SELECT Round(2345.7634,-2) FROM dual;

  This query will give result as 2300. Let me explain this. If, we gave negative precision then the database will replace those many digits to the left of decimal point with '0' and will round that number to nearest whole number. In this example we gave precision as -2. So, it will replace 4,5 with two '0' s. After replacing this, preceding number will be decided by nearest whole number concept. Nearest whole number to 345 is 300.
So, we will get result as 2300.

   SELECT Round(2366.7634,-2) FROM dual;

 This query will give result as  2400 as nearest whole number to 366 after replacing two '0' s is 400.

This is how we will use Round() function.

Trunc() Function ::


  This function is used to truncate the value or column or result of an expression to specified decimal places. This function is similar to Round() function but the only difference is here rounding of rules in mathematics are not applicable.

 Ex : if we are truncating 423.456734 to 2 decimal places then result will be 423.45.

 syntax :: Trunc(input number, precision).

   Here also the precision will decide the number of decimal places to be present in the output.

Ex : SELECT Trunc(4235.67895,2) From dual;

  This will give us result as 4235.67. If we do rounding of on this we will get, 4235.68 due to rounding of rules. But truncate will just truncate the number to specified decimal places.

  If we exclude the precision in the syntax then by default database will take it as zero.

 Ex : SELECT Trunc(4235.67895) FROM dual;

  This will give us result as 4235.

 If we give precision as negative value then the numbers to the left of decimal point will be truncated.

 Ex : SELECT Trunc(4235.67985) FROM dual;

  This will give us result as 4200. Here also nearest whole number concept will not be followed. This will just replace those many number of digits specified in negative precision with '0' s.

 Mod() Function ::


    This function will be used to find the remainder of a division of a number with another number. This will contain two input numeric arguments.

 Syntax ::  Mod(number1,number2).

    This will give us the remainder of division number1/number2.

  Ex : Select Mod(17,5) From Dual;

    This will give us the result as 2.

 Division operator which we have discussed in previous posts will give us quotient of the division. If we want to find out the remainder of the division then we have to use the Mod function.

 These are the different numeric functions which are available in SQL.