Spotfire: count the number of a certain character in a string

Question

I am trying to add a new calculated column that counts the number of semi colons in a string and adds one to it. So the column i have contains a bunch of aliases and I need to know how many for each row.

For example, A; B; C; D

So basically this means there are 4 aliases (3 semi colons + 1)

Need to do this for over 2 million rows. Help please!

Answer 1

Basic idea is to subtract length of your string without ; characters from it's original length:

len([columnName])-len(Substitute([columnName],";",""))+1

Answer 2

Here it is with a regular expression:

Len(RXReplace([Column 1], "(?!;).", "", "gis"))+1

RXReplace takes as arguments:

1. The string you are wanting to work on (in this case it is on Column 1)
2. The regular expression you want to use (here it is (?!;). )
3. What you want to replace matches with (blank in this situation so that everything that matches the regex is removed)
4. Finally a parameter saying how you want it to work (we are passing in gis which means replace all matches not just the first, ignore case, replace newlines)

We wrap this in a Len which gives us the amount of semicolons since that is all that is left and finally we add 1 to it to get the final result.

You can read more about the regular expression here: https://msdn.microsoft.com/en-us/library/az24scfc(v=vs.110).aspx but in a nutshell it says match everything that isn't a semi colon.

You can read more about RXReplace and Len here: https://docs.tibco.com/pub/spotfire/6.0.0-november-2013/userguide-webhelp/ncfe/ncfe_text_functions.htm