count substring within a string

[Joe Blow]

Hello All:

First off, thank you so much for your help. It is truly appreciated.

I am trying to write an expression in a query that counts how many times a
particular string of data repeats in a text field.

ie: THE BUS IS A VERY BUSY BUSINESS.

I ask, how many times does the exact string "BUS" show up, and the result
should be 3. One for BUS, another for BUSY and another for BUSINESS. Can't
use InStr because that only returns the first position...

Any ideas how to write this column in an Access 2003 query?

Thanks,

Joe

 

[fredg]

Hello All:

First off, thank you so much for your help. It is truly appreciated.

I am trying to write an expression in a query that counts how many times a
particular string of data repeats in a text field.

ie: THE BUS IS A VERY BUSY BUSINESS.

I ask, how many times does the exact string "BUS" show up, and the result
should be 3. One for BUS, another for BUSY and another for BUSINESS. Can't
use InStr because that only returns the first position...

Any ideas how to write this column in an Access 2003 query?

Thanks,

Joe

InStr can return more than just the first instance.
it depends upon from where in the string you start looking.
See the lines marked '* below.
In a module:
Function CountString(StringIn as String, LookFor as String) as String
Dim intX as Integer
Dim intY as Integer
intX = InStr(StringIn,LookFor) '* start from the 1st position
Do while intX <> 0
intY = intY + 1

'*start at the intX + 1 position
intX = InStr(intX + 1,StringIn,LookFor)
Loop
CountString = intY
End Function

Call it from a query.

CountInString:CountString([FieldName],"bus")

This also works (without using a mocule).

CountInString: (Len([FieldName])-Len(Replace([FieldName],"bus","")))/3

The /3 part is whatever the number of characters in the search string
(i.e. "bus") is.

 

[Dirk Goldgar]

Hello All:

First off, thank you so much for your help. It is truly appreciated.

I am trying to write an expression in a query that counts how many
times a particular string of data repeats in a text field.

ie: THE BUS IS A VERY BUSY BUSINESS.

I ask, how many times does the exact string "BUS" show up, and the
result should be 3. One for BUS, another for BUSY and another for
BUSINESS. Can't use InStr because that only returns the first
position...

Any ideas how to write this column in an Access 2003 query?

I think you'll have to call a user-defined function; like this one,
perhaps:

'----- start of code -----
Function fncCountString(LookIn As Variant, LookFor As Variant) As Long

' Returns the number of times the second (string) argument
' is found in the first (string) argument.
'
' Written by: Dirk Goldgar, DataGnostics
' Published for public use -- please retain attribution.

Dim lCount As Long
Dim sLookIn As String
Dim sLookFor As String

If IsNull(LookIn) Or IsNull(LookFor) Then
fncCountString = 0
Else
lCount = UBound(Split(LookIn, LookFor))
If lCount < 0 Then
fncCountString = 0
Else
fncCountString = lCount
End If

End If

End Function
'----- end of code -----

With the above function stored in a standard module, you can call it
from a query if you like.

 

[Joe Blow]

Fred, CountInString: (Len([FieldName])-Len(Replace([FieldName],"bus","")))/3
worked like a charm.

Smiling happy camper here.


THANKS!!!


Joe

Hello All:

First off, thank you so much for your help. It is truly appreciated.

I am trying to write an expression in a query that counts how many times
a
particular string of data repeats in a text field.

ie: THE BUS IS A VERY BUSY BUSINESS.

I ask, how many times does the exact string "BUS" show up, and the result
should be 3. One for BUS, another for BUSY and another for BUSINESS.
Can't
use InStr because that only returns the first position...

Any ideas how to write this column in an Access 2003 query?

Thanks,

Joe

InStr can return more than just the first instance.
it depends upon from where in the string you start looking.
See the lines marked '* below.
In a module:
Function CountString(StringIn as String, LookFor as String) as String
Dim intX as Integer
Dim intY as Integer
intX = InStr(StringIn,LookFor) '* start from the 1st position
Do while intX <> 0
intY = intY + 1

'*start at the intX + 1 position
intX = InStr(intX + 1,StringIn,LookFor)
Loop
CountString = intY
End Function

Call it from a query.

CountInString:CountString([FieldName],"bus")

This also works (without using a mocule).

CountInString: (Len([FieldName])-Len(Replace([FieldName],"bus","")))/3

The /3 part is whatever the number of characters in the search string
(i.e. "bus") is.

 

[Dirk Goldgar]

InStr can return more than just the first instance.
it depends upon from where in the string you start looking.
See the lines marked '* below.
In a module:
Function CountString(StringIn as String, LookFor as String) as String
Dim intX as Integer
Dim intY as Integer
intX = InStr(StringIn,LookFor) '* start from the 1st position
Do while intX <> 0
intY = intY + 1

'*start at the intX + 1 position
intX = InStr(intX + 1,StringIn,LookFor)
Loop
CountString = intY
End Function

Call it from a query.

FWIW, I tried this approach as an alternative to the Split-based version
I posted. I benchmarked them both, and was surprised to find that the
version using Split() was much faster than the version using InStr().

This also works (without using a mocule).

CountInString: (Len([FieldName])-Len(Replace([FieldName],"bus","")))/3

The /3 part is whatever the number of characters in the search string
(i.e. "bus") is.

Cool! I never thought of this one! Brilliant!