Count of specific character in a text

[shahaba]

I have a list of texts and I need to find out the number of times each character shows up.

Example in the text "76DC4-34" the character '4' shows up twice. Is there a query that for this instance if I provide the text and character I'm looking for it will give the number of occurences as a result or if there is no such query how do I go about getting the result I want?

I assume I'm going to have to repeat a process 36 times (10 for each numeric digit and 26 for each letter, other characters in the text don't matter)

[ADezii]

I have a list of texts and I need to find out the number of times each character shows up.

Example in the text "76DC4-34" the character '4' shows up twice. Is there a query that for this instance if I provide the text and character I'm looking for it will give the number of occurences as a result or if there is no such query how do I go about getting the result I want?

I assume I'm going to have to repeat a process 36 times (10 for each numeric digit and 26 for each letter, other characters in the text don't matter)

I do not think that there is an easy Method of doing what you describe, but the following Function should point you in the right direction. It will count the Total Number of occurrences of each Number contained within the String passed to it:

Public Function fCountCharacters(strString As String) As String
Dim intLenOfString As Integer
Dim intCounter As Integer
Dim strResult As String
Dim intNumOf0s As Integer
Dim intNumOf1s As Integer
Dim intNumOf2s As Integer
Dim intNumOf3s As Integer
Dim intNumOf4s As Integer
Dim intNumOf5s As Integer
Dim intNumOf6s As Integer
Dim intNumOf7s As Integer
Dim intNumOf8s As Integer
Dim intNumOf9s As Integer

If Len(strString) = 0 Then Exit Function

intLenOfString = Len(strString)

For intCounter = 1 To intLenOfString
  Select Case Mid$(strString, intCounter, 1)
    Case "0"
      intNumOf0s = intNumOf0s + 1
    Case "1"
      intNumOf1s = intNumOf1s + 1
    Case "2"
      intNumOf2s = intNumOf2s + 1
    Case "3"
      intNumOf3s = intNumOf3s + 1
    Case "4"
      intNumOf4s = intNumOf4s + 1
    Case "5"
      intNumOf5s = intNumOf5s + 1
    Case "6"
      intNumOf6s = intNumOf6s + 1
    Case "7"
      intNumOf7s = intNumOf7s + 1
    Case "8"
      intNumOf8s = intNumOf8s + 1
    Case "9"
      intNumOf9s = intNumOf9s + 1
  End Select
Next

strResult = "Number of 0s: " & Format$(intNumOf0s, "00") & vbCrLf
strResult = strResult & "Number of 1s: " & Format$(intNumOf1s, "00") & vbCrLf
strResult = strResult & "Number of 2s: " & Format$(intNumOf2s, "00") & vbCrLf
strResult = strResult & "Number of 3s: " & Format$(intNumOf3s, "00") & vbCrLf
strResult = strResult & "Number of 4s: " & Format$(intNumOf4s, "00") & vbCrLf
strResult = strResult & "Number of 5s: " & Format$(intNumOf5s, "00") & vbCrLf
strResult = strResult & "Number of 6s: " & Format$(intNumOf6s, "00") & vbCrLf
strResult = strResult & "Number of 7s: " & Format$(intNumOf7s, "00") & vbCrLf
strResult = strResult & "Number of 8s: " & Format$(intNumOf8s, "00") & vbCrLf
strResult = strResult & "Number of 9s: " & Format$(intNumOf9s, "00") & vbCrLf
  fCountCharacters = strResult
End Function

Debug.Print fCountCharacters("76DC4-34")

OUTPUT:

Number of 0s: 00
Number of 1s: 00
Number of 2s: 00
Number of 3s: 01
Number of 4s: 02
Number of 5s: 00
Number of 6s: 01
Number of 7s: 01
Number of 8s: 00
Number of 9s: 00

[missinglinq]

Here's a function that is a little more compact. It uses the Replace() function, so you'll have to be using Access 2000 or later for it to work. It covers everything you've specified, 0-9 and A -Z.

Public Function CountAllCharacters(ParsedString As String) As String
Dim ReportString As String
ReportString = ""

For x = 48 To 90
 If x > 57 And x < 65 Then GoTo Skip2MeLoop
 cnt = Len(ParsedString) - Len(Replace(ParsedString, Chr(x), ""))

If cnt <> 0 Then
 If ReportString = "" Then
   ReportString = "Number of " & Chr(x) & "'s: " & cnt
 Else
  ReportString = ReportString & vbCrLf & "Number of " & Chr(x) & "'s: " & cnt
 End If
End If
Skip2MeLoop:
Next x

CountAllCharacters = ReportString

End Function

This will give you an output, for the sample string 76DC4-34 of

Number of 3's: 1
Number of 4's: 2
Number of 6's: 1
Number of 7's: 1
Number of C's: 1
Number of D's: 1

If you need to show zero for all the characters that do not appear, a simple modification will do that.

Welcome to Bytes!

Linq ;0)>

[ChipR]

Linq, that's brilliant!

[FishVal]

Not bad, not bad.

However I don't think iterating ASCII values is a good idea.
I suppose a better way is to:

• Get the first character.
• Replace this character with nothing throughout the string and get length difference.
• Return to Step 1 until the string is empty.

This gives a more efficient code and allows (I'm almost sure though haven't checked this) to work with unicode strings.

Regards,
Fish.

[ADezii]

Here's a function that is a little more compact. It uses the Replace() function, so you'll have to be using Access 2000 or later for it to work. It covers everything you've specified, 0-9 and A -Z.

Public Function CountAllCharacters(ParsedString As String) As String
Dim ReportString As String
ReportString = ""

For x = 48 To 90
 If x > 57 And x < 65 Then GoTo Skip2MeLoop
 cnt = Len(ParsedString) - Len(Replace(ParsedString, Chr(x), ""))

If cnt <> 0 Then
 If ReportString = "" Then
   ReportString = "Number of " & Chr(x) & "'s: " & cnt
 Else
  ReportString = ReportString & vbCrLf & "Number of " & Chr(x) & "'s: " & cnt
 End If
End If
Skip2MeLoop:
Next x

CountAllCharacters = ReportString

End Function

This will give you an output, for the sample string 76DC4-34 of

Number of 3's: 1
Number of 4's: 2
Number of 6's: 1
Number of 7's: 1
Number of C's: 1
Number of D's: 1

If you need to show zero for all the characters that do not appear, a simple modification will do that.

Welcome to Bytes!

Linq ;0)>

Nice job Linq! Your code is definitely more compact, efficient, practical, portable, digestible, etc., etc., etc.

[ADezii]

Not bad, not bad.

However I don't think iterating ASCII values is a good idea.
I suppose a better way is to:

• Get the first character.
• Replace this character with nothing throughout the string and get length difference.
• Return to Step 1 until the string is empty.

This gives a more efficient code and allows (I'm almost sure though haven't checked this) to work with unicode strings.

Regards,
Fish.

Hello FishVal, just out of sheer curiosity, why don't you think that iterating ASCII values is a good idea? Would there be that much of a difference iterating ASCII Values as opposed to their Character Representations ?

[FishVal]

Hello FishVal, just out of sheer curiosity, why don't you think that iterating ASCII values is a good idea? Would there be that much of a difference iterating ASCII Values as opposed to their Character Representations ?

• This is inefficient because not all characters are expected to be in a string being processed.
• This is completely unacceptable for unicode strings.

[ADezii]

• This is inefficient because not all characters are expected to be in a string being processed.
• This is completely unacceptable for unicode strings.

Thanks Fish, couldn't the ChrW() Function be used to return a String containing a Unicode character as well as the Standard ASCII Characters from a String?

[FishVal]

It certainly could, but there is no reason to search a string for all unicode characters.

[missinglinq]

Thank you for pointing this out, FishVal, but until you entered the thread I don't remember anyone talking about unicode strings. Did I miss something?

Of course, taking the first letter of the string and replacing all instances of it, making a note of how many instances there were, then repeating this thru the string, would leave you with

1) A final string that listed the characters in the order that they appeared, not 0-9 and A-Z order

2) Having to account for repeating characters so that they weren't counted twice.

3) Having to account for characters other than 0-9 and A-Z, which is all that the OP stated they were concerned with.

Linq ;0)>

[FishVal]

Thank you for pointing this out, FishVal, but until you entered the thread I don't remember anyone talking about unicode strings. Did I miss something?

:D Well. The initial question was not about counting all characters at all.

Of course, taking the first letter of the string and replacing all instances of it, making a note of how many instances there were, then repeating this thru the string, would leave you with

1) A final string that listed the characters in the order that they appeared, not 0-9 and A-Z order

Results array sorting would not take more time than running Replace() function for all possible characters.

2) Having to account for repeating characters so that they weren't counted twice.

They will not since they are excluded from string when found.

3) Having to account for characters other than 0-9 and A-Z, which is all that the OP stated they were concerned with.

Don't you like to go slightly beyond of what you guess about OP's question? :D

Kind regards,
Fish.

[OldBirdman]

The original question is to count the number of occurrences for a given character. Function would return a single value, from 0 to n, the length of the original string. If this is a field in a query, then SELECT tbl.StringToTes t AS [Match]. . . fCountOccur([Match],"A") AS CountA . . . FROM tbl

Public Function fCountOccur(strSource As String, strMatch As String) As String
Dim iCount As Integer
Dim iPosition As Integer
    iCount = 0
    For iPosition = 1 To Len(strSource)
        If Mid(strSource, iPosition, 1) = strMatch Then iCount = iCount + 1
    Next
    fCountOccur = Format(iCount, "000")
End Function

One query could do all 36 conditions. Design grid would have 36 fields beyond original table. They might be CountA: fCountOccur([Match],"A"), CountB: fCountOccur([Match],"B"), etc.
These 36 fields could be concatinated into one long string, such that the count for 0's is in positions 1-3; the count for 1's in positions 4-6; etc. therefore, there are mid(strLongStri ng,n*3+1,3) n's, n from 0 to 9. Letters would be n=Asc(letter)-55. Concatination could be in query or with an enclosing loop in function, in which case 2nd argument would not be used as inner loop would use ascii value from outer loop.

OldBirdman

[ADezii]

Count of Specific Character in a String

Sorry guys, but I still feel that Linq's approach, or a variation thereof, is the way to go on this one!

[missinglinq]

No, ADezii! You don't think we should try to see how #$%^&* complicated we could make this? I mean, what if some of the strings were in Klingon? Would it work then? Or in Esperanto and encrypted? Would it work then?

Linq ;0)>