Chapter 14 looked at several functions that allow us to return parts of data. This chapter looks at a few more functions that let you combine values in different ways and even perform calculations. Depending on the nature of the data you’re working with, I’m sure you’ll find some functions in this chapter that you’ll use frequently. If you work with address data, for example, 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 show the desired precision of currency?
This chapter looks at these scenarios and more. We’ll start with using functions to combine values.
15.1 Combining string values
I haven’t discussed this topic yet, but you can use SQL to perform basic calculations, such as addition. Here’s an example of basic addition (result shown in figure 15.1):
SELECT 1 + 1;
Figure 15.1 The results of calculating 1+1 using SQL
Being able to perform addition is useful if you’re working exclusively with numeric data, but what if you need to combine string values? Unfortunately, as you can see in figure 15.2, using the plus sign (+) doesn’t allow you to combine string values to get a desired result:
SELECT 'I' + ' ' + 'love' + ' ' + 'books!';
Figure 15.2 The results of combining string values with the plus operator, which doesn’t combine all the values into a string
