13. Math and Text

Finally, we get to some operators you're probably more familiar with, our old friends:

• Addition: +
• Subtraction: -
• Multiplication: *
• Division: /

in SQL, these four operators are used exactly like they were in elementary school, to perform arithmetic operations on numbers. But with SQL, we can use the operators both on literal numbers (e.g., the number 5) and on columns that contain numeric data types (e.g., the amount column in our transactions table).

Math with literals

This is pretty simple: you can use these operators just like you would in the rest of your life to add, subtract, divide, and multiply numbers. For example:

Feel free to play around in the console above testing out different types of arithmetic operations. For bonus points, try adding NULL to some of your expressions and see what happens, for example (note that our SQL console will show - in place of NULL):

select NULL+8

More advanced math

More advanced mathematical operations are possible in all dialects of SQL (e.g., exponents, roots, modulo, logarithms, trigonometric functions, etc.) However, generally the syntax for these operators is specific to the dialect of SQL used by the database in question. In this course, we will not cover use of these types of mathematical operations. You can usually find a quick guide to these operators for your database by googling, for example, "Exponent operator in PostgreSQL/Oracle/SQL Server/etc."

Math with column values

Where these operators become really useful is in performing operations on values in our database. This is accomplished by using a column reference to the column in place of a literal number. For example, we can return the value of every gift in the transaction table plus $5 as follows:

As you can see, by selecting amount + 5 in our amount_plus_5 column, we get a column with each value from Transactions incremented by $5.

Exercise 1: Math with Columns

An ask string is needed for an upcoming campaign. To get started on this, Membership has asked for a list of all transactions from November 2024 that includes:

• account id
• transaction date
• transaction amount
• transaction amount times 2

Write an SQL query to generate this list.

Working with Text

Unfortunately, performing operations on text is one area where there can be a big difference in syntax between different dialects of SQL. A full treatment of every type of operation in every dialect is beyond the scope of this course. However, we are going to cover some basics that will be more than enough for the vast majority of your work.

Combining text

Text can generally be combined in one of two ways. The first and simplest is to use an operator between text much as you would to add two numbers (or the & operator in Excel). Most databases support the double pipe (||) for text concatenation with the notable exception of Microsoft SQL Server (powers Raiser's Edge) which uses +. Examples:

In MS SQL Server, the equivalent would be (try this in the console if you want to see a long error message):

select
    'abc' + 'def' as six_letters,
    'A more' + ' ' + 'complex' + ' ' + 'example' as complex

The second method is using the concat() function. This function is supported by most databases and takes a comma-separated list of strings that should be concatenated. For example:

Finally, as we saw with numbers, you can also use these operators on column values directly:

Exercise 2: String Concatenation

For an upcoming campaign, your mailshop needs a list of everyone's address, but they can't handle having separate columns for street address, city, state, and zip code. Instead, they want one single column that results in data of the form:

123 Birch Ave, Anytown, OH 12345

Write an SQL query to generate this column, along with account id, for every row in the addresses table.

Substring operations

Often, we will need to pull out some part of a string of text. In SQL, this is known as a substring operation and unfortunately there are differences in how each dialect of SQL implements this functionality. Most databases use the substring() function for this purpose. A notable exception is Oracle (used by ROI CRM) which uses the substr() function. DuckDB, which powers our console, accepts both.

substring() and substr() each take three arguments in the same order:

1. The string we are starting from
2. The start position for our substring
3. The number of characters we want in our substring

For example, if we wanted to get the 2nd and 3rd characters from my name, we might write:

This tells the database, "Take the string 'Adam' and give me a substring that starts with the 2nd character and is 2 characters in length". As you can see, the result is da.

Most databases also supply the left() and right() functions for convenience. These two functions work exactly like their counterparts in Excel. You specify the string you're starting with and how many characters you want, and they give you the leftmost/rightmost characters you asked for. For example:

Finally, note that just like our other operators, you can use database columns in place of literal strings, for example:

select 
    left(first_name,1) as first_letter_of_first_name
from accounts

Exercise 3: Substring

You need to supply a list of all source codes in the database that includes:

• Source Code ID
• Source Code
• 2nd and 3rd character of source code
• 4th character of source code
• Last character of source code

Write an SQL query to generate this list.