As in most programming languages, SQL has functions for processing data. It is worth noting that, unlike SQL statements, functions are not standardized for all types of DBMS, that is, to perform the same operations on data, different DBMSs have their own function names. This means that the query code written in one DBMS may not work in another, and this should be taken into account in the future. This mostly applies to functions for processing text values, converting data types and manipulating dates.
Most DBMSs support a standard set of function types, namely:
The SQL implementation in MS Access has the following functions for text processing:
| Function | Description |
|---|---|
| Left() | Selects the characters in the text from the left |
| Right() | Selects the characters in the text on the right |
| Mid() | Selects characters from the middle of the text |
| UCase() | Converts characters to upper case |
| LCase() | Converts characters to lower case |
| LTrim() | Removes all blank characters to the left of the text |
| RTrim() | Removes all blank characters to the right of the text |
| Trim() | Removes all blank characters from both sides of the text |
Let's convert product names to uppercase using the UCase() function:
Run SQLSELECT Product, UCase(Product) AS Product_UCase
FROM Sumproduct
Let's separate the first three characters in the text using the LEFT() function:
Run SQLSELECT Product, LEFT(Product, 3) AS Product_LEFT
FROM Sumproduct
Number processing functions are designed to perform mathematical operations on numerical data. These functions are designed for algebraic and geometric calculations, so they are used much less often than date and time processing functions. However, numeric functions are the most standardized across all versions of SQL. Let's look at the list of numerical functions:
| Function | Description |
|---|---|
| SQR() | Returns the square root of the specified number |
| ABS() | Returns the absolute value of a number |
| EXP() | Returns the exponent of the specified number |
| SIN() | Returns the sine of the specified angle |
| COS() | Returns the cosine of the specified angle |
| TAN() | Returns the tangent of the specified angle |
We have listed only a few of the main features, but you can always refer to your DBMS documentation for a full list of supported features with detailed descriptions.
For example, let's write a query to obtain the square root of the numbers in the Amount column using the SQR() function:
Run SQLSELECT Amount, SQR(Amount) AS Amount_SQR
FROM Sumproduct
Date and time manipulation functions are among the most important and frequently used SQL functions. In databases, date and time values are stored in a special format, so they cannot be used directly without additional processing. Each DBMS has its own set of functions for processing dates, which, unfortunately, does not allow them to be transferred to other platforms and SQL implementations.
List of some functions for handling date and time in MS Access:
| Function | Description |
|---|---|
| DatePart() | Returns part of a date: year, quarter, month, week, day, hour, minute, second |
| Year(), Month() | Returns the year and month respectively |
| Hour(), Minute(), Second() | Returns the hour, minutes, and seconds of the specified date |
| WeekdayName() | Returns the name of the day of the week |
Let's look at an example of how the DatePart() function works:
SELECT Date1, DatePart("m", Date1) AS Month1
FROM Sumproduct
The DatePart() function has an additional parameter that allows us to display the required part of the date. In the example, we used the value of the parameter "m", which displays the month number (in the same way, we can display the year - "yyyy", the quarter - "q", the day - "d", the week - "w", the hour - "h" , minutes - "n", seconds - "s", etc.).
Statistical functions help us get ready data without sampling it. SQL queries with these functions are often used to analyze and generate various reports. An example of such selections can be: determining the number of rows in a table, obtaining the sum of values for a certain field, searching for the largest/smallest or average value in the specified column of the table. We also note that statistical functions are supported by all DBMS without any special changes in writing.
List of statistical functions in DBMS Access:
| Function | Description |
|---|---|
| COUNT() | Returns the number of rows in a table or column |
| SUM() | Returns the sum of the values in a column |
| MIN() | Returns the smallest value in a column |
| MAX() | Returns the largest value in a column |
| AVG() | Returns the average value in a column |
Returns the number of all rows in the table:
Run SQLSELECT COUNT(*) AS Count1
FROM Sumproduct
Returns the number of all non-empty rows in the Product field:
SELECT COUNT(Product) AS Count2
FROM Sumproduct
We intentionally removed one value in the Product column to show the difference in performance between the two queries.
Run SQLSELECT SUM(Quantity) AS Sum1
FROM Sumproduct
WHERE Month = 'April'
With this request, we displayed the total amount of goods sold in the month of April.
Run SQLSELECT SUM(Quantity*Amount) AS Sum2
FROM Sumproduct
As you can see, in statistical functions we can also perform calculations on several columns using standard mathematical operators.
Run SQLSELECT MIN(Amount) AS Min1
FROM Sumproduct
Run SQLSELECT MAX(Amount) AS Max1
FROM Sumproduct
Run SQLSELECT AVG(Amount) AS Avg1
FROM Sumproduct
