Solving Problem using In-Built Functions

Every Database Management Systems come with built in functions and stored procedures. It is very important for the database users to get familiar with them. This makes a life of DBA or a developer easy. Knowing them all or at least getting familiar will prevent you from re-inventing the wheel.

There are so many SQL built in functions in DBMS. It is always a good idea to know that they exist and know the basic functions of each of them. You are not required to know the syntax of all. MSDN has a list of all the inbuilt functions which are available for MS SQL Server. You can refer to msdn documents for syntax, descriptions, examples etc. Their list is more up-to-date than the blogs you might find online. Similarly, other database systems like Oracle, MySQL and other have similar list of functions available for use to use.

In this article, I am going to show you how you can use built in function to resolve some of the issues that you might encounter.

Problem: This is the problem that I was asked: Your client requested you to add prefix of MR/Mrs for all the employee of a company.

Solution: There are more than one ways to get this done. Let’s try and get this problem solved using as many functions as possible. The sole purpose is to get you introduce to more than one functions and get the same result.

Solution 1:
String Concatenation Function: Concatenation combines and display MR while selecting first name from employee table.

SQL Syntax:

SELECT 'MR ' + fname 
FROM   employee;  

Solution 2 :
Update: Here we will update a table with MR for all employee.

SQL Syntax;

UPDATE employee 
SET    fname = ’mr’ + fname; 
SELECT fname 
FROM   employee; -- Verifying the changes.   

Now, with your select statement, you can view the update made to employee table. The update statement  changed all the employee regardless of their sex and you know the problem right MR Jessica!!. I did this on purpose to introduce a rollback concepts and apply more built in functions.

Let's write a rollback script: Rollback script is something I write before implementing a change in production. You should be able to back-out the change anytime. Here's my way of writing rollback script. First, we will come up with a select statement to ignore the prefix from the table. Next, convert select into update statement.

SQL Syntax:

SELECT RIGHT(fname, Len(fname)-2;
SELECT Substring(fname, 3, Len(fname))
SELECT reverse(LEFT(Reverse(fname, Len(fname)-2))
SELECT Replace(fname, ‘pr’,’’)  

The above select syntax does remove the prefix MR first name. Now let’s convert select statement to update statement.

SQL Syntax:

UPDATE employee 
SET    fname = RIGHT(fname, Len(fname)-2;
--now, CONVERT the rest SELECT statements.

We rolled back the changes we did. You now that a clean set of data to work. We haven’t resolve the issue of applying  the correct prefix for male and female employees yet.

SQL Syntax:

UPDATE employee 
SET    fname = ‘mrs ’ + fname 
WHERE  sex =’’female’;

UPDATE employee
SET fname = ‘mr ’ + fname WHERE sex =’male’;

I hope this article has helped you think in multiple ways to solve a problem. And also, jump started with the built in SQL Functions. Explore more of them, you will simply love the power of these functions and not having to write yourself.