Monarch Tips

Count Occurrences of a Character or String Inside a Data Field

A recent post on the Monarch discussion forum asked whether it was possible to create a calculated field that counts how many instances of a particular character occurs in a field.

For example, if we have a character field named INFO in our Monarch table with a value such as:

172.1.2.3,jdoe,dell,windows xp,2734 mhz,1024 meg

a calculated field that counts the commas (",") should return 5

Congratulations to "Monarch Black Belt" Sandy Cavalaris, aka "Data Kruncher," for the following clever calculated field formula:

Len(INFO)- Len(Strip(INFO,","))

As Sandy explains, this formula determines how many characters are in the original field if you take out the commas (using the LEN function combined with the STRIP function). The result is then deducted from the number of characters in the original string.

With slight modifications, the same logic will let you count the number of times a desired sting appears within a field. For example, suppose our field named INFO contained data such as this:

Bloggs,Smith,Bloggs,Jones,Johnson

If we wanted to count the number of times the name "Bloggs" appears in the string, the following formula will yield the desired result of 2:

(Len(INFO)- Len(Replace (INFO,"Bloggs",""))) /Len("Bloggs")

This time we determine how many characters are in the original field if you remove each instance of the desired string (using the REPLACE function this time, replacing each instance of the desired string with nothing). That value is then deducted from the number of characters in the original string. Finally, this result is then divided by the length of the desired string itself.

Monarch forum "Grand Master" Grant Perkins also noted if the desired character or string was taken from a calculated field set up as a Runtime Parameter, the Monarch user would have a flexible way to specify what he/she wanted to count.

It is also important to mention that the STRIP and REPLACE functions are both case sensitive. The STRIP function exists in Monarch V7 and V8; REPLACE exists only in Monarch V8.

The best single source of advice, tips and tricks for Monarch and Monarch Data Pump is our online forum at: http://mails.datawatch.com/cgi-bin/ultimatebb.cgi