If you write SQL on a regular basis, it is important to know the functions that are supported by your DBMS. For DB2, there are four types of built-in functions that can be used to transform data in your tables:

  • Aggregate functions, sometimes referred to as column functions, compute, from a group of rows, a single value for a designated column or expression.
  • Scalar functions are applied to a column or expression and operate on a single value.
  • Table functions can be specified only in the FROM clause of a query and return results resembling a table.
  • Row functions, new as of DB2 11, of which there is only one, UNPACK. It returns a row of values that are derived from unpacking a binary string

Understanding the built-in functions available to you within DB2 can make many coding tasks much simpler. Functions, many times, can be used instead of coding your own application logic to perform the same tasks. You can gain a significant advantage using DB2 built-in functions because you can be sure they will perform the correct tasks with no bugs... as opposed to your code which requires time to code, stringent debugging, and in-depth testing. This is time you can better spend on developing application specific functionality.

At any rate, I was recently asked how to return a count of specific characters in a text string column. For example, given a text string, return a count of the number of commas in the string.

Well, if you know DB2 functions you know that there isn’t one specifically focused on this task. But digging a little deeper, you can see how this can be done using a combination of two scalar functions, LENGTH and REPLACE, as shown here:

SELECT LENGTH(TEXT_COLUMN) - LENGTH(REPLACE(TEXT_COLUMN, ',' ''))

The first LENGTH function simply returns the length of the text string. The second iteration of the LENGTH function in the expression returns the length of the text string after replacing the target character (in this case a comma) with the zero-length string. By subtracting the entire length, from the (perhaps) shortened length, we get a count of the specific character instances in the strihng.

So, let's use a string literal to show a concrete example:

SELECT LENGTH('A,B,C,D') - LENGTH(REPLACE('A,B,C,D', ',', '')) 

This will translate into 7 - 4... or 3. And, indeed, there are three commas in the string.

When confronted with a problem like this it is usually a good idea to review the list of built-in SQL functions to see if you can accomplish your quest using SQL alone. Any time you can avoid the overhead of building an application program it makes sense to do so… from the perspectives of both the amount of time expended and the overall efficiency of the process.