Wednesday, May 25, 2011

A Quick SQL Trick: Find The Number of Commas

Today's blog post is a short one. 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 the LENGTH and REPLACE functions as follows:

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 will translate into 7 - 4... or 3. And 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.


3 comments:

Marcin Wiśnios said...

I like it. Simple and ingenious. Regards!

Anonymous said...

LENGTH() command returns the length of the column and not the data length. Using DB2v8. Any idea?

Craig S. Mullins said...

The length of fixed-length strings will include blanks. The length of a varying-length string is the actual length, not the maximum length.

You could use the RTRIM function to strip off trailing blanks, which would also result in a variable-length string...