Issue with function is that they tend to be DBMS specific. Only few are supported by all popular DBMS. Although function is the same the implementations is not. Therefore they are not portable.
Portable: Code that is written so that it will run on multiple systems.
So if we decide to use functions, we need to make sure to comment code well.
Most of SQL Implementations support the following types of functions:
- Text function
- Numeric functions
- Date and time functions
Text Manipulation Functions
We have already seen example – the RTRIM so let’s see another example, UPPER ()
SELECT vend_name, UPPER (vend_name) AS
ORDER BY vend_name;
Commonly Used Text-Manipulation Functions
LEFT () , RIGHT()
LOWER () / LCASE() if using Acces , UPPER() / UCASE() if using Acces
LENGHT () also DATALENGHT() or LEN ()
LTRIM() , RTRIM()
Date and Time Manipulation Functions
are some of the most important functions in the SQL. Unfortunately, they tend to be the least consistent and least portable.
Lets see some example. we need to retrieve list of all orders made in 2005:
SQL SERVER and SYBASE
WHERE DATEPART ( yy, order_date) = 2004;
it doesn't have DATEPART(). WE can use function named YEAR()
WHERE YEAR (order_date) = 2005
So as wee seen these functions are DBMS specific.
Numeric Manipulation Functions
These function are usually most uniform and consistent.
ABS () Returns a number’s absolute value
EXP () Returns the exponential value of a specific number
COS () Returns the trigonometric cosine of a specifies angle
SIN () Returns the trigonometric sine of a specified angle
TAN () Returns the trigonometric tangent of a specified angle
SQRT ()Returns the square root of a specified number
PI () Returns the value of PI