In this post, we will learn about aggregate and string functions in SQL.
1. Aggregate Functions
In some cases, the information we need is not actually stored in the database but we can retrieve it by computing in some ways from the stored data.
We will discuss various functions along with their usage with example
| Function | Description |
| SUM( ) | Return the sum of all values in an expression. |
| AVG( ) | Calculates average value of an expression. It ignores NULL values. |
| MAX( ) | Returns maximum value of the set of values in expression. |
| MIN( ) | Returns minimum value of the set of values in expression. |
| COUNT( ) | Returns the count of the items in expression. |
Example 1
mysql> SELECT max(salary), min(salary) from employee;
| max(salary) | min(salary) |
| max(salary) | 10000.00 |
1 row in set (0.00 sec)
Example 2
Same as above using alias for columns.
mysql> SELECT max(salary) "Maximum", min(salary) "Minimum" from employee;
| Maximum | Minimum |
| 70000.00 | 10000.00 |
1 row in set (0.00 sec)
Example 3
Total sum of the salaries of all employees.
mysql> SELECT sum(salary) from employee;
| sum(salary) |
| 215000.00 |
Continuing our session on aggregate and string functions in SQL, we will learn about string function in dbms now.
2. String Functions
| Function | Description |
| CONCAT(s1, s2,..sn) | Concatenates s1, s2, ..sn in to single string |
| LEFT(s,x) | Returns the first x characters from string s |
| RIGHT(s,x) | Returns the last x characters from string s |
| LENGTH(s) | Returns the number of characters in string s |
| RPAD(s,x,pad) | Pads string s from right with pad until it contains x characters |
| LPAD(s,x,pad) | Pads string x from left with pad until it contains x characters. |
| SUBSTRING(s,x,y) | Returns a substring of length y characters starting from position x in string s. |
| INSTR(s1,s2) | Returns the position where string s2 occurs in string s1. |
| LOCATE(s1, s2) | Returns the position where string s1 occurs in string s2. |
| STRCMP(s1,s2) | Compares string s1 with string s2 based on their ASCII values (not based on the length). Returns 0 if they are same, 1 if the first is greater and -1 if the second is greater. |
| REPLACE(s,x,y) | Replaces all occurrences of x in string s with y. |
| REPEAT(s,x) | Repeats string x for x times. |
| REVERSE(s) | Reverses the string s. |
| UCASE(s) | Returns string s with all characters in upper case. |
| LCASE(s) | Returns string s with all characters in lower case. |
| ASCII | Returns the ASCII value of first character. |
Example 1
Merge fname with lname.
mysql> SELECT concat(fname, lname) "Full Name" from employee;
| Full Name |
| NeelimaPadmavar |
| AlokPawar |
| SharadaJadhao |
| VaishnaviMunigial |
4 rows in set (0.01 sec)
Example 2
Merge fname with lname with space between both.
mysql> SELECT concat(fname, space(3), lname) "Full Name" from employee;
| Full Name |
| Neelima Padmavar |
| Alok Pawar |
| Sharada Jadhao |
| Vaishnavi Munigial |
4 rows in set (0.01 sec)
Example 3
Use of left function
mysql> SELECT left(dob, 4) "Birth Year" from employee;
| Birth Year |
| 1982 |
| 1985 |
| 1974 |
| 1963 |
4 rows in set (0.08 sec)
Example 4
User of right function
mysql> SELECT right(address, 3) from employee;
| right(address, 3) |
| une |
| une |
| bad |
| ded |
4 rows in set (0.00 sec)
Example 5
Use of right function for selected values.
mysql> SELECT right(address, 3) from employee where fname=‘sharada’;
| right(address, 3) |
| bad |
| bad |
2 rows in set (0.05 sec)
Example 6
User of length function.
mysql> SELECT fname, length(fname) from employee;
| fname | length(fname) |
| Neelima | 7 |
| Alok | 4 |
| Sharada | 7 |
| Vaishnavi | 9 |
4 rows in set (0.00 sec)
Example 7
User of read and lpad.
mysql> SELECT rpad(fname, 9, '*'), lpad(fname, 12, '@') from employee;
| rpad(fname, 9, ‘*’) | lpad(fname, 12, ‘@’) |
| Neelima** | @@@@@Neelima |
| Alok***** | @@@@@@@@Alok |
| Sharada** | @@@@@Sharada |
| Vaishnavi | @@@Vaishnavi |
4 rows in set (0.00 sec)
Example 8
Use of substring function
mysql> SELECT substring(address, 1, 10) from employee;
| substring(address, 1, 10) |
| Dhayari, Pune |
| Balaji nagar |
| Shivaji nagar |
| VIP road |
4 rows in set (0.00 sec)
Example 9
If s2 is found in s1
mysql> SELECT eid, address from employee where instr(address, ‘aj’) > 0;
| eid | address |
| 102 | Balaji nagar, Pune |
| 103 | Shivaji nagar, Aurangabad |
2 rows in set (0.00 sec)
Example 10
If s2 is not found in s1.
mysql> SELECT eid, address from employee where instr(address, ‘aj’) = 0;
| eid | address |
| 101 | Dhayari, Pune |
| 104 | VIP road, Nanded |
| 105 | Monda, Nashik |
| 106 | Nagar Road, Aurangabad |
4 rows in set (0.00 sec)
Example 11
Returns the position of ‘aj’
mysql> SELECT instr(address, 'aj') from employee;
| instr(address, ‘aj’) |
| 0 |
| 4 |
| 5 |
| 0 |
4 rows in set (0.00 sec)
Example 12
Returns the position of ‘aj’
mysql> SELECT locate('aj', address) from employee;
| locate(‘aj’, address) |
| 0 |
| 4 |
| 5 |
| 0 |
4 rows in set (0.00 sec)
Example 13
Comparison between s1 and s2
mysql> SELECT fname, lname, strcmp(fname, lname) from employee;
| fname | lname | strcmp(fname, lname) |
| Neelima | Padmawar | -1 |
| Alok | Pawar | -1 |
| Sharada | Jadhao | -1 |
| Vaishnavi | Munigial | -1 |
4 rows in set (0.00 sec)
Example 14
Change the address using replace function.
mysql> SELECT address "Before Change" replace(address, 'nagar', 'colony') "After Change" FROM employee WHERE address like '%nagar%';
| Before Change | After Change |
| Balaji nagar, Pune | Balaji colony, Pune |
| Shivaji nagar, Aurangabad | Shivaji colony, Aurangabad |
| Nagar Road, Aurangabad | Nagar Road, Aurangabad |
3 rows in set (0.00 sec)
In this example, two points are to be noted
- In the replace() function, we are using small n in ‘nagar’ and so it replaces only ‘nagar’ and not ‘Nagar’.
- In the like predicate of where clause, we use small n in ‘nagar’ but like predicate is not case sensitive and so it displays all records having ‘nagar’ or ‘Nagar’ or any other that matches this spelling (whether it may be ‘NAgaR’ also).
Example 15
Use of repeat function
mysql> SELECT repeat(lname, 3) from employee where lname like 'P%';
| repeat(lname, 3) |
| PadmawarPadmawarPadmawar |
| PawarPawarPawar |
| PatilPatilPatil |
Example 16
Use of reverse function
mysql> SELECT reverse('neelima');
| reverse(‘neelima’) |
| amileen |
That’s end of our post on aggregate and string functions in SQL.