Functions in SQL

 

Functions are a very important topic in SQL, and they are used almost everywhere in our queries. In this blog, I will explain the concept of functions and its pre-defined types available to us.

What is a function ?

Stored piece of program that manipulates submitted data and return some value. A function is a set of SQL statements that perform a specific task. Instead of rewriting the SQL query again and again, we can simply call that function. A function accepts inputs in the form of parameters and returns a value.

SQL Server comes with a set of built-in functions that perform a variety of tasks.

                                                          

Where to use the functions ?

We can use functions in our queries, let’s see some examples –

• SELECT UPPER(name) FROM students

In the above example, we are using the UPPER function.

• SELECT * FROM students WHERE SUBSTRING ('SQL is FUN!’,1,3)

In the above example, we are using the SUBSTRING function.

 

What different types of functions are available ?

We have various types of functions available to us, but they are broadly classified as Single Row functions and Multiple Row/Group functions.

 

        

The different types of functions available are as follows –

• String Functions

• Numeric Functions

• String / Number Conversion Functions

• Group Functions

• Date and Time Functions

• Date Conversion Functions

 

• String functions

These functions are used to do manipulation on string data.

 

1. LOWER( string )

All letters are changed to lowercase.

Example -  AMAR à amar

 

2. UPPER( string )

All letters are changed to uppercase.

Example - amar à AMAR

 

3. INSTR( string, find, pos, occ)

Finds string within a string.

Example - INSTR(‘Amar’, ‘ar’) à 3

                  INSTR(‘Amar’, ‘a’,1,2) à 3

 

4. LENGTH( string)

Tells length of string.

Example - LENGTH(‘Amar’) à 4

 

5. LPAD( string, length, pad-string)

Adds string to its left.

Example - LPAD(‘Amar’,10) à ‘      Amar’

                  LPAD(‘Amar’,10, ‘-’) à ‘------Amar’

 

6. RPAD( string, length, pad-string)

Adds string to its right.

Example - RPAD(‘Amar’,10) à ‘Amar      ’

                  RPAD(‘Amar’,10, ‘-’) à ‘Amar------’

 

7. LTRIM( string, characters)

Trims out string from left.

Example - LTRIM(‘    Amar’) à ‘Amar’

                 

8. RTRIM( string, characters)

Trims out string from right.

Example - RTRIM(‘Amar    ’) à ‘Amar’

 

9. SUBSTR( string, pos, number)

Takes out string from string.

Example - SUBSTR(‘Amar’, 3) à ‘ar’

                  SUBTR(‘Amar’, 3, 1) à ‘a’

                 

 

• Numeric functions

These functions are used to do manipulation on numeric data.

 

1. ABS( value)

Returns absolute value.

Example - ABS(-13) à 13

 

2. MOD( value, divisor)

Returns remainder of value divided by divisor.

Example - MOD(4,2) à 0

 

3. POWER( value, exp)

Raises power of value to its exponent.

Example - POWER(2,2) à 4

 

4. ROUND( value, decimal)

Rounds value to decimal places.

Example - ROUND (20.6, 0) à 21

 

5. CEIL ( value)

Rounds value upwards.

Example - CEIL(20.6, 0) à 21

                  CEIL(-20.6, 0) à 20

 

6. FLOOR ( value)

Rounds value downwards.

Example - FLOOR(20.6, 0) à 20

                  FLOOR(-20.6, 0) à 21

 

7. SQRT ( value)

Returns Square root of value.

Example - SQRT(36) à 6

                  SQRT(81) à 9

 

 

• String / Number Conversion Functions

These functions are used to convert string data to numeric data and vice-versa.

 

1. TO_CHAR ( value, format)

Converts value into string using format.

Example - TO_CHAR(100.11, $999.9) à ‘$100.1’

                        TO_CHAR(100.11, 999) à ‘100’

 

2. TO_NUMBER ( string, format)

Converts string into number using the format specified.

Example - TO_NUMBER(‘100.11’, 999.9) à 100.1

 

3. NANVL ( value, format)

If value NaN (Not a Number), it will replace the value.

Example - NANVL(‘Amar’, 15) à 15

                        NANVL(10, 15) à 10

 

 

• Group Functions

These functions are used to do manipulation on a collection or rows or multiple rows of a table.

 

1. Count (column)

Returns number of rows.

Example - Count (*) from orders à 140

                  Count (customer_id) from orders WHERE customer_id=4 à 9

 

2. MAX (column)

• Returns maximum value in column.

Example - MAX(marks) from students à 99

                  SELECT student_name from students WHERE marks = MAX(marks)

 

3. MIN (column)

• Returns minimum value in column.

Example - MIN (marks) from studentsà 10

                 SELECT student_name from students WHERE marks = MIN(marks)

 

4. SUM (column)

• Returns sum of column.

Example - SUM (quantity) from orders à 900

                 SELECT SUM(quantity) from orders WHERE customer_id= 7

 

5. AVG (column)

• Finds average of column

Example - AVG (rent) à $1300

                 SELECT AVR(rent) from country WHERE city_id= 1

 

 

• Date and Time Functions

These functions are used to do manipulation on date and time data.

 

1. CURRDATE() / CURRTIME() / SYSDATE() / NOW()

Returns the current date or time.

 

2. ADDDAYS() / ADDMONTHS() / ADDYEARS()

Adds days, months and years, respectively.

 

3. MONTHS_BETWEEN(date1, date2)

Tells the number of months between date1 and date2.

Example - MONTHS_BETWEEN( TO_DATE ('2003/08/02', 'yyyy/mm/dd’), TO_DATE ('2003/06/02’, yyyy/mm/dd’)) à 2

 

4. SYSTIMESTAMP() / CURRTIMESTAMP() / TIMESTAMP ()

Returns current date and time as a timestamp.

 

 

• Date Conversion Functions

These functions are used to convert string data to date-time data and vice-versa.

 

1. TO_CHAR ( date, format)

Converts date into string using format.

Example -  TO_CHAR(now(), ‘dd-mm-yyyy’) à 16-8-2014

 

2. TO_DATE ( string, format)

Converts string into date using format.

Example - TO_DATE ('2014/08/16', 'yyyy/mm/dd')