Aggregate And String Functions in SQL

Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners. Also, if you are interested in content writing, you can mail us at tutorialwing@gmail.com.

In this post, we will learn about aggregate and string functions in SQL.

  1. Aggregate Function
  2. String Function

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.

Support Us

If you like Tutorialwing and would like to contribute, you can email an article on any educational topic at tutorialwing@gmail.com. We would love to publish your article. See your article on Tutorialwing and help others with your knowledge. Follow Facebook, LinkedIn, Google+, Twitter, Youtube for latest updates.
Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners