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

vend_name_upcase

FROM Vendors

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

SELECT order_num

FROM Orders

WHERE DATEPART ( yy, order_date) = 2004;

 

MySQL

it doesn't have DATEPART(). WE can use function named YEAR()

SELECT order_num

FROM Orders

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

Anonymous