built-in SQL functions
Introduction – The SQL so far is part of the standard that all SQL implementations. Now let’s about the most useful, but most difficult areas of SQL – built-in SQL functions.
Types of functions –
Like most computer languages, SQL includes the capability to perform functions on data. However, each database vendor has implemented their own set of functions. Not only does each vendor choose which functions they will support, different vendors implement the same functionality using different syntax! This basically means that as soon as you open the door to functions, your database code stops being portable. For traditional database applications, that is less of an issue, but for web-based applications using tools like PHP, it means that the SQL functions you embedded in your web application will break when you move from MySQL to Oracle or to SQL-Server. To make matters worse, SQL functions often provide significant performance gains for all sorts of database manipulation, particularly in the case of web applications.
So what’s a we should to do? My advice is to use functions anywhere you can produce a measurable performance gain and document thoroughly what database the SQL was intended to work with. As we’ll talk about in a future column, moving any function-oriented code to a database stored procedure and essentially “black-boxing” it is an excellent approach — this lets the DBA rewrite the stored procedures for optimal performance — but some databases do not support stored procedures, so this is not a perfect solution. There are a number of functions that are available in a wide range of databases, so you can usually get the functionality you need as long as the functions are not too exotic.
There are four basic types of functions –
1) Numeric functions – for statistical, trigonometric, and other mathematical operations,
2) Text functions – for formatting and manipulating text values,
3) Time/date functions – used to parse date values as well as manipulate the date itself, and
4) System functions – for returning database-specific information (such as a username).
How do you know what functions your database supports? Unfortunately, the only reliable way is to check the documentation. As a general rule of thumb, the more a database costs (or the more widely it is used for open source databases), the more functions it supports. Functions open up a huge range of additional functionality for SQL developers, but at the cost of portability between database platforms. However, functions provide improved functionality and performance for data-intensive applications.
How to use functions?
Functions in SQL are typically used in WHERE clauses, though they generally can be used anywhere you’d use a field name or value. One of the easiest ways to learn functions is to see a few in action. We’ll start with a SELECT statement to find names longer than 12 characters…
Step(1) Create Table – student –
mysql> use SQL_function;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> CREATE TABLE student ( name varchar(40) );
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show tables;
+————————+
| Tables_in_SQL_function |
+————————+
| student |
+————————+
1 row in set (0.00 sec)
mysql> desc student;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| name | varchar(40) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
1 row in set (0.00 sec)
Step(2) INSERT Few records in student table –
mysql> INSERT INTO student VALUES(‘Arun Bagul’);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO student VALUES(‘Sri madhanvan’);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO student VALUES(‘Hari madhanvan’);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT name FROM student;
+—————-+
| name |
+—————-+
| Arun Bagul |
| Sri madhanvan |
| Hari madhanvan |
+—————-+
3 rows in set (0.00 sec)
mysql>
Step(3) Use functions in WHERE clause –
mysql> SELECT name FROM student WHERE length(name) > 12 ;
+—————-+
| name |
+—————-+
| Sri madhanvan |
| Hari madhanvan |
+—————-+
2 rows in set (0.00 sec)
mysql>
Thank you,
Arun Bagul