Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Genius has limits, stupidity has none.
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

17
October

How Do I Find the No. Times a Character Appears in a String Field in Crystal Reports?

I was recently asked, "How do I find the number of times a character appears in a string field in Crystal Reports XI?"

For example:

If the string field contained the text julia emelogue, and the character one was looking for was "e", then the answer would be 3.


Solution:

There are a number of formulas one can use to calculate the number of times a specified character appears within a field, including loops.

But the simplest method uses a formula which contains two functions:
  • Unbound - Used to determine the size of an array
  • Split - Splits a string field into separate array values

The final formula:

Ubound(Split({TableName.FieldName},"x"))-1

where x = the specified character to be counted

Combines the functions which results in the formula splitting the string field into separate array elements, and then counting the number of times the specified character occurs within the string

Note:

This formula is case-sensitive, so if the field contains the text Julia Emelogue and one searches for the character "e" in the formula, the value will be 2

The formula can be modified to take into consider all the specified characters within the string field, regardless of case:

Ubound(Split({TableName.FieldName},"x",-1,1))-1



If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us
1

Amar

15
May
Hi, This is exactly what I've been looking for. You've made my work a lot easier. Thanks a lot...!!!
2

Martin Halford

02
July
What if my input is a string, not an array? I have an array in my main report produced by this formula: stringVar Segments; WhileReadingRecords; Segments:=Segments + ({Table.Field}) + ","; Segments But when I link it to a subreport and use as {?Pm-@Segments} it appears as a string. Now I need to break that comma-delimited string into individual fields for use in my select statements. Can you help?
3

Julia

07
September
Hi Martin,

If you are working with an Array, I do not think this solution is what you are after.

Why are you wanting to split your array in your SELECT statements?

The {?PM-@Segments} is purely there to connect your sub report values to its corresponding Main report, so its your main report that is driving the record selection.

You can therefore simply use the SPLIT function on the Parameter field, or alternatively look at my Displaying Parameter values blog post.

Cheers Joolz
4

MOHAN

17
February
Hi when i used this formula i got some error in my database i have 3 types of classification just i need count the number of grades like a=20,b=15,c=30 i need specific count. please help me

MAKE A COMMENT

Name *

Email Address *

Comment *

To prove you are human what is:

15 + 57 =