Identifying the Total Number of Occurrences for a Particular Letter

[sandybam]

Hi All,

Is there a way to count how many times a letter appears in a field? I will have a string of characters such as PPPPLLLLPPPPPBBPPPP and would like to have one query that will tell me how many Ps, Ls, Bs and so on are in that field for each record.

Thank you for your help!

Sandy

 

[Golom]

Not with just SQL (this is the Jet SQL Forum) but you can do it easily with code.

Sub CountChars(myString As String)
Dim Counts(255) As Integer
Dim n           As Integer

For n = 1 To Len(myString)
    Counts(Asc(Mid$(myString, n, 1))) = Counts(Asc(Mid$(myString, n, 1))) + 1
Next n

For n = 0 To 255
   If Counts(n) > 0 Then
      Debug.Print Chr$(n), Counts(n)
   End If
Next n

End Sub

 

[MichaelRed]

I 'suppose' that you could use a function like this:

Public Function basCharCnt(myStr As String, MyChr As String) As Integer

    'Michael Red    12/5/03 Tek-Tips thread701-723147
    'Return the count of a specific Character in a string.  Please Note this is _
     NOT production level code.  It does NOT have any error checking, and does _
     NOT provide for ANY variation (e.g. provide Discrimination between Upper and Lower Case).

    'Example Usage: _
     ? basCharCnt("PPPPLLLLPPPPPBBPPPP", "P")
    '13

    Dim ChrCnt As Variant
    Dim Idx  As Integer

    ChrCnt = Split(myStr, MyChr)
    basCharCnt = UBound(ChrCnt)

End Function

as the function "buried" within a local query for specific characters, and add multiple fields to get the commonly used "printable" characters. You obviously cannot count all possible characters, even within a single character set, as Ms. A. limits the number of fields in a recordset.

I AM curious as to what pratical use the information would be to anyone?




MichaelRed
m.red@att.net

Searching for employment in all the wrong places