Count for multiple occurrence of an expression in a string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to count number of '.' (dots) in an expression e,g. asd.rty.de.com
(answer = 3 dots at positions 4,8,11 respectively) . Instr functions gives
only the first occurrence.
Any help is appreciated
Thanks
Anupam
 
What comes redily to mind is a function that accepts two arguments:
the string to analyze and the comparison character. Look in Help for
Mid. The function would return an number of times the comparison
character was found in the string as an integer.

HTH
 
Thanks for the response.
I am using Windows 2003, but these functions are not available. Do I need to
install something ?
 
Thanks for your response. I will look into it.

Larry Daugherty said:
What comes redily to mind is a function that accepts two arguments:
the string to analyze and the comparison character. Look in Help for
Mid. The function would return an number of times the comparison
character was found in the string as an integer.

HTH
Click to expand...
 
Do you mean Access 2003 (not Windows 2003)? The functions are definitely
there.

How are you trying to use the functions? (i.e.: where have you put Allen's
code?) What happens when you use them? Do you get an error message? If so,
what's the error?
 
The version of Windows does not matter.

UBound() has been in Access for ever.
Split() has been part of Access since 2000.
 
Here's a function that does this type of counting:

' ***************************************
' ** Function CountCharacterOccurences **
' ***************************************

Public Function CountCharacterOccurences(ByVal strStringToSearch As Variant,
_
ByVal strCharacterToFind As String) As Long
' *** THIS FUNCTION COUNTS THE NUMBER OF TIMES A SINGLE CHARACTER IS FOUND
' *** IN A TEXT STRING. THE SEARCH IS CASE-INSENSITIVE.
' *** THE FUNCTION'S VALUE IS THE NUMBER OF TIMES THE SINGLE CHARACTER IS
FOUND.

Dim lngCountChars As Long, lngPosition As Long

On Error Resume Next

lngCountChars = 0
'truncate sChar in case it is longer than one character
strCharacterToFind = Left(strCharacterToFind, 1)

For lngPosition = 1 To Len(strStringToSearch)
'if character is found, increment the counter
If Mid(strStringToSearch, lngPosition, 1) = strCharacterToFind Then _
lngCountChars = lngCountChars + 1
Next lngPosition

CountCharacterOccurences = lngCountChars
Exit Function
End Function
 
I think we need another possible solution.
=Len([YourExpression]) - Len(Replace([YourExpression],".",""))

Len("asd.rty.de.com") - Len(Replace("asd.rty.de.com",".","")) = 3
 
My apologies, I meant to say that I am using Access 2003
I am using it in SQL query, probably that is again my mistake.
Need guidance on how to use it.
Thanks
 
Duane said:
I think we need another possible solution.
Click to expand...

As this is the tabledesign group, what about a data-driven solution?

If the OP has a Sequence table of integers (seq), parsing and counting
is easy:

SELECT T2.data_col, COUNT(S1.seq) AS tally
FROM Test2 AS T2, [Sequence] AS S1
WHERE MID$(T2.data_col, S1.seq, 1) = '.'
AND S1.seq BETWEEN 1 AND 20
GROUP BY T2.data_col

Here's the VBA to fully reproduce:

Sub CountChar()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection

' Create Sequence table seq = 1 to 100
.Execute _
"CREATE TABLE [Sequence] ( seq INTEGER NOT" & _
" NULL PRIMARY KEY)"
.Execute _
"INSERT INTO [Sequence] (seq) VALUES (1);"
.Execute _
"INSERT INTO [Sequence] SELECT Units.nbr" & _
" + Tens.nbr AS seq FROM ( SELECT nbr FROM" & _
" ( SELECT 0 AS nbr FROM [Sequence] UNION" & _
" ALL SELECT 1 FROM [Sequence] UNION ALL" & _
" SELECT 2 FROM [Sequence] UNION ALL SELECT" & _
" 3 FROM [Sequence] UNION ALL SELECT 4 FROM" & _
" [Sequence] UNION ALL SELECT 5 FROM [Sequence]" & _
" UNION ALL SELECT 6 FROM [Sequence] UNION" & _
" ALL SELECT 7 FROM [Sequence] UNION ALL" & _
" SELECT 8 FROM [Sequence] UNION ALL SELECT" & _
" 9 FROM [Sequence] ) AS Digits ) AS Units," & _
" ( SELECT nbr * 10 AS nbr FROM ( SELECT" & _
" 0 AS nbr FROM [Sequence] UNION ALL SELECT" & _
" 1 FROM [Sequence] UNION ALL SELECT 2 FROM" & _
" [Sequence] UNION ALL SELECT 3 FROM [Sequence]" & _
" UNION ALL SELECT 4 FROM [Sequence] UNION" & _
" ALL SELECT 5 FROM [Sequence] UNION ALL" & _
" SELECT 6 FROM [Sequence] UNION ALL SELECT" & _
" 7 FROM [Sequence] UNION ALL SELECT 8 FROM" & _
" [Sequence] UNION ALL SELECT 9 FROM [Sequence]" & _
" ) AS Digits ) AS Tens WHERE Units.nbr +" & _
" Tens.nbr BETWEEN 2 AND 100 "

' Create test table
.Execute _
"CREATE TABLE Test2 (data_col VARCHAR(20)" & _
" NOT NULL);"
.Execute _
"INSERT INTO Test2 (data_col) VALUES" & _
" ('asd.rty.de.com');"

' Show results parsed
Dim rs
Set rs = .Execute( _
"SELECT T2.data_col, S1.seq AS pos, MID$(T2.data_col," & _
" S1.seq, 1) AS data_char FROM Test2 AS T2," & _
" [Sequence] AS S1 WHERE MID$(T2.data_col," & _
" S1.seq, 1) = '.' AND S1.seq BETWEEN 1 AND" & _
" 20")
MsgBox rs.GetString
rs.Close

' Show results tally
Set rs = .Execute( _
"SELECT T2.data_col, COUNT(S1.seq) AS tally" & _
" FROM Test2 AS T2, [Sequence] AS S1 WHERE" & _
" MID$(T2.data_col, S1.seq, 1) = '.' AND" & _
" S1.seq BETWEEN 1 AND 20 GROUP BY T2.data_col")
MsgBox rs.GetString
rs.Close

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
 
Back
Top