SQL Basics: Built-In Functions

Although the SQL1 standard doesn’t specify them, most SQL implementations include a number of useful built-in functions. These facilities often provide data type conversion facilities. For example, DB2’s built-in MONTH() and YEAR() functions take a DATE or TIMESTAMP value as their input and return an integer that is the month or year portion of the value. This query lists the name and month of hire for each salesperson in the sample database:

 SELECT NAME, MONTH(HIRE_DATE)

FROM SALESREPS

and this one lists all salespeople hired in 1988:

 SELECT NAME, MONTH(HIRE_DATE)

FROM SALESREPS

WHERE YEAR(HIRE_DATE) = 1988

Built-in functions are also often used for data reformatting. Oracle’s built-in TO_ CHAR() function, for example, takes a DATE data type and a format specification as its arguments and returns a string containing a formatted version of the date. In the results produced by this query:

 SELECT NAME, TO_CHAR(HIRE_DATE,’DAY MONTH DD, YYYY’)

FROM SALESREPS

the hire dates will all have the format “Wednesday June 14, 1989” because of the built- in function.

In general, a built-in function can be specified in a SQL expression anywhere that a constant of the same data type can be specified. The built-in functions supported by popular SQL dialects are too numerous to list here. The IBM SQL dialects include about two dozen built-in functions, Oracle supports a different set of about two dozen built-in functions, and SQL Server has several dozen. The SQL2 standard incorporated the most useful built-in functions from these implementations, in many cases with slightly different syntax. These functions are summarized in Table 5-6.

Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.

Leave a Reply

Your email address will not be published. Required fields are marked *