14 Querying with functions

 

Chapter 12 looked at a handful of functions—commands that perform some sort of predefined calculation. We looked specifically at basic aggregate functions that allow us to quickly calculate things like the sum of a range of values, as well as the minimum, maximum, and average values for a given range.

This chapter examines even more functions that open more possibilities in SQL, including those that allow us to select and filter specific string, date and time, and other informational values. First, though, we’ll take a broader look at when we should and shouldn’t use functions.

14.1 The problems with functions

Functions are incredibly useful for selecting specific parts of values, calculating values, and manipulating values in SQL. They’re like magic spells we can perform by adding an extra word in our SQL. Functions, however, have two big problems that we need to discuss before we use them throughout our queries.

14.1.1 Function commands vary for each RDBMS

The core keywords and clauses we’ve used up to now are universal for the most part. When we write SQL using SELECT, FROM, WHERE, and GROUP BY, we know that the code will work not only in MySQL but also in any relational database management system (RDBMS) we use. Functions, however, are not universal, and many of the functions we examine in this chapter have some variation for one or more RDBMSes.

14.1.2 Function commands can be inefficient

14.2 String functions

14.2.1 Case functions

14.2.2 Trim functions

14.2.3 Other string functions

14.3 Date and time functions

14.3.1 Date functions that return numeric values

14.3.2 Date functions that return string values

14.3.3 Other date and time functions

14.4 Informational functions

14.4.1 Date and time information

14.4.2 Connection information

14.5 Lab

14.6 Lab answers