15 Combining or calculating values with functions
In the previous chapter, we looked at several functions that allow us to return parts of data. In this chapter, we will look at a few more functions that let us combine values in different ways, and even perform calculations. Depending on the nature of the data you are working with, I’m sure you’ll find some functions in this chapter that you will be using frequently.
For example, if you work with address data, how can you combine all the columns for street, city, and more into a single column? Or if you work with financial reports, how can you make all your calculations correctly show the desired precision of currency?
We’re going to look at these scenarios and more in this chapter. Let’s start with how we can use functions to combine values.
15.1 Combining string values
We haven’t discussed it yet, but you can use SQL to perform basic calculations, such as addition. Here’s an example of basic addition, with the result shown in figure 15.1:
SELECT 1 + 1;
Figure 15.1 The results of calculating 1 + 1 using SQL
This is great news if you are working exclusively with numeric data, but what if you need to combine string values? Unfortunately, as we can see in figure 15.2, using the plus sign doesn’t allow us to combine string values to a desired result:
SELECT 'I' + ' ' + 'love' + ' ' + 'books!';
Figure 15.2 The results of combining string values with the plus operator, which does not combine all the values into a string
