Useful SQL Functions
I mentioned earlier that a problem with relational databases is that they separate data across different tables. Some other problems are:
We choose a format in which to store data e.g. storing dates in short format DD/MM/YYYY
We try and store data to conserve storage space e.g. 'M' for male & 'F' for female
We remove values that can be calculated e.g. the number of days an employee has worked for a company
to mention but a few.
These apparent shortcomings can be overcome using SQL and in particular some useful functions that are available in SQL.
(please remember that we are working with Access which has it's own functions that may not work in other RDBMS packages)
Let's look at some functions that can be beneficial to you in building SQL Queries
| UCASE | Converts a field to upper case e.g. UCASE(EmpName) |
| LCASE | Converts a field to lower case e.g. LCASE(EmpName) |
| MID | Extract characters from a text field e.g. MID(EmpName,start[,end]) will return the EmpName from position start until position [end]. If the end criteria is omitted then everything from start to the end of the string will be returned |
| LEN | Returns the length of a text field e.g. LEN(EmpName) |
| INSTR | Returns the Numeric position of a named character within a text field e.g. INSTR(EmpName,'A') |
| LEFT | Return the left part of a text field requested e.g. LEFT(EmpName,3) will return the first 3 characters of EmpName) |
| RIGHT | Return the right part of a text field requested e.g. RIGHT(EmpName,3) will return the last 3 characters of EmpName) |
| ROUND | Will round a numeric field to the number of decimals specified e.g. ROUND(Salary,2) will round Salary to 2 decimal places |
| MOD | Returns the remainder of a division operation e.g. MOD(x,y). if x = 3 and y = 2 then the answer would be 1. |
| NOW | Returns the current system date |
| FORMAT | Used to change the way a field is displayed. Particularly useful for date fields e.g. FORMAT(Birthday,"Short Date") (See below for other valid formats) |
| DATEDIFF | Used to perform date calculations. (See below for other valid formats) |
FORMAT Command Syntax
if we had a field Birthday that contained the value 22-02-2001
| FORMAT Command | Output |
| FORMAT(Birthday,"short date") | 22/02/2001 |
| FORMAT(Birthday, "long date") | Thursday, February 02, 2001 |
| FORMAT(Birthday, "medium date) | 22/FEB/2001 |
| FORMAT(Birthday, "DD/MM/YYYY") | 22-02-2001 |
| FORMAT(Birthday, "DDD/MM/YYYY") | Thu/02/2001 |
| FORMAT(Birthday, "DD/MMM/YYYY") | 22/FEB/2001 |
| FORMAT(Birthday,"DD/MMMM/YYYY") | 22/February/2001 |
| FORMAT(Birthday, "DD") | 22 |
| FORMAT(Birthday, "MM") | 02 |
| FORMAT(Birthday, "YYYY") | 2001 |
I think you get the idea....
DATEDIFF Command Syntax
Date2 is Greater then Date1
| DATEDIFF Command | Output |
| DATEDIFF("d",Date1,Date2) | Number of days between Date2 and Date1 |
| DATEDIFF("m",Date1,Date2) | Number of months between Date2 and Date1 |
| DATEDIFF("YYYY",Date1,Date2) | Number of years between Date2 and Date1 |