If you write SQL on a regular basis, it is very important to know the functions that are supported by your DBMS. In general, there are three 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.
Understanding the built-in functions available to you can make many coding tasks much simpler. Functions, many times, can be used instead of coding your own application program to perform the same tasks. You can gain a significant advantage using 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.
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 a blank.
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 translates 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.