Arithmetic Operators ColdFusion Help

Arithmetic operators are used to perform mathematical calculations on fields. Some of them can also be used to work with strings. There are four arithmetic operators described

When working with more than one arithmetic operator, there is precedence when evaluating their calculations. They are resolved in this order: multiplication, division, addition, and subtraction. Of course, you can use parentheses to override this order.


The Multiply (*) operator is used to multiply two values. This can be done in both the SELECT and the WHERE clauses. This operator can only be used with numerical values. This symbol is also used as a wildcard for selecting all fields from a table when used w.ith the SEL:ECT clause. I isting 10.18 and Figure 10.18 show.the use of the multiply operator in both the SELECT and the WHERE clauses. In the SELECT clause it is used to calculate a new book price that includes a 20% discount off the current price. In the WHERE clause it is used to ensure that only books that have a final price of over $10, after the discount has been applied, will be included.

Multiplying Values


The Divide (/) operator is used to divide one value into another. This can be done in both the SELECT and the WHERE clauses. This operator can only be used with numerical values. If you want to cut your book prices in half as long as the final price is over $5.00, you could use the following query:

SELECT     speaker_books. book title.
(speaker_books_book-price / 2) as half_price
From  speaker books
WHERE      (speaker_books.book_price / 2) > 5


The Add (+) operator has two main uses. The first is to add numeric values together. This can be done in both the SELECT clause and the WHERE clause. In the SELECT clause, you would most likely use it to add a set numeric value to a column. The following query would add $5.00 to every book price listed in the speaker_books table if the field already has a value:

SELECT        speaker_books.book_title.
( + 5) as new_price
FROM speaker_books
WHERE         speaker_books.book_price IS NOT NULL

The second use of the Add (+) operator is to concatenate two string values. Again, this can be done in both the SELECT and WHERE clauses. The following example illustrates the use of the Add operator in both clauses. In the SELECT clause, it is used to concatenate the speaker’s first and last name so that it is available to Coldfusionas a single field named ‘full_name’. In the WHERE clause, it is used to concatenate the same two fields to see if they meet the string criteria to the right of the operand.

SELECT      (speakers.speaker_firsCname +
speakers.speaker_last_name) as full_name.
FROM speakers
WHERE        (speakers.speaker_first_name +
speakers.speaker_last_name) 2 Guy Kawasaki’


The Subtract (-) operator can be used to subtract one value from another. This can be done in both the SELECT and the WHERE clauses. This operator can only be used with numerical values. In the following example, $1 is subtracted from all book prices in the speaker_books table where the book_pri ce field is not NULL:

Logicai/BOOLEAN/Conjunctive Operators

Logical operators are used to bind together multiple conditions in the WHERE clause. They return only the Boolean values of TRUE and FALSE. There are three logical operators, and they are described


NOT is used in front of a condition to reverse its result. If the condition returns TRUE, then NOT changes it to FALSE. If a condition returns FALSE, then NOT changes it to TRUE. NOT can be used in front of any condition to change the results. The following example returns all records in the speaker.jiooks table where the book_price is less than 5:

FROM Speaker_books
WHERE NOT speaker_books.book_price > 5

Of course, you can get the same results by just switching the greater than sign to a less than sign. NOT can also be used right alongside several of the following operators:



If there is more than one condition in your WHERE clause and both conditions must be fulfilled, then you would use the AND operator to join them together. A record is returned only if that record said all of the conditions separated by the AND operator. The following query would return ‘allbooks with greater than,$5 and less than $10:

FROM speaker_books
WHERE speaker_books.book_price > 5
AND speaker_books.book_price < 10

You might think you could use the BETWEEN operator instead. However, the BETWEEN operator would include book prices that are $5 and $10. To use the BETWEEN operator and get the same results, you would need to use this WHERE clause: WHERE speaker_books. book_pri ce’ BETWEEN 6 AND 9.


If there is more than one condition in your WHERE clause and any of the conditions can be fulfilled to return a TRUE result, then you would use the OR operator to join the conditions together-A record is returned only if that record satisfies any of the conditions separated bythe OR operator. The results for the following query and all records will be returned if !he speaker’ title includes any of the three words author, director, orfounder:

SELECT    speaker_title.speaker_id
FROM speaker_title
WHERE .   speaker_title.speaker_title LIKE ‘%author%’
OR             speaker_title.speaker_title LIKE ‘%director%’
OR             speaker_title. speaker_title LIKE ‘%founder%’

Posted on November 14, 2015 in Using CFQuery and SQL to Interact with the Database

Share the Story

Back to Top
Share This