469,327 Members | 1,290 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,327 developers. It's quick & easy.

Counting specific char's in a string

MLH
MyString = "All men are created equal"
Debug.PrintLen(MyString)

Now that's easy. But how about just counting
the letter "e"? Or, if I were curious to know
how many commas were in the string. How
might one do this in a query?

Suppose a table had single string field with
100 records, each containg a sentence
from a textbook. What would the SQL look
like in a query that listed all 100 sentences
and the number of e's appearing in each
sentence?
Mar 4 '07 #1
4 18367
Replace the character with nothing, and the difference in length is the
count of the character instances.

The query would be something like this:
SELECT Len([MyField]) - Len(Replace([MyField], ",", "")) AS CountOfCommas
FROM Table1;

This should work in Access 2002, 2003, and 2007.
It won't work in A97 or earlier.
In A2000, you may strike this problem:
http://support.microsoft.com/kb/225956

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MLH" <CR**@NorthState.netwrote in message
news:rq********************************@4ax.com...
MyString = "All men are created equal"
Debug.PrintLen(MyString)

Now that's easy. But how about just counting
the letter "e"? Or, if I were curious to know
how many commas were in the string. How
might one do this in a query?

Suppose a table had single string field with
100 records, each containg a sentence
from a textbook. What would the SQL look
like in a query that listed all 100 sentences
and the number of e's appearing in each
sentence?
Mar 4 '07 #2
Look for InStr in the help file

"MLH" <CR**@NorthState.netwrote in message
news:rq********************************@4ax.com...
MyString = "All men are created equal"
Debug.PrintLen(MyString)

Now that's easy. But how about just counting
the letter "e"? Or, if I were curious to know
how many commas were in the string. How
might one do this in a query?

Suppose a table had single string field with
100 records, each containg a sentence
from a textbook. What would the SQL look
like in a query that listed all 100 sentences
and the number of e's appearing in each
sentence?

Mar 4 '07 #3
Hi -

This will work in A97.

Function StrCount(ByVal TheStr As String, theItem As Variant) As Integer
'------------------------------------------------------------------
' Purpose: Counts the numbers of times an item occurs
' in a string.
' Coded by: raskew
' Arguments: TheStr: The string to be searched.
' TheItem: The item to search for.
' Returns: The number of occurences as an integer.
'
' Note: To test: Type '? StrCount("The quick brown fox jumped over
' the lazy dog", "the") in the debug window.
' The function returns 2.
'------------------------------------------------------------------
Dim i As Integer
Dim j As Integer
Dim placehold As Integer
Dim strHold As String
Dim itemhold As Variant

strHold = TheStr
itemhold = theItem
j = 0

If InStr(1, strHold, itemhold) 0 Then
While InStr(1, strHold, itemhold) 0
placehold = InStr(1, strHold, itemhold)
j = j + 1
strHold = Mid(strHold, placehold + Len(itemhold))
Wend
End If
StrCount = j
End Function

HTH - Bob

Jeff Smith wrote:
>Look for InStr in the help file
>MyString = "All men are created equal"
Debug.PrintLen(MyString)
[quoted text clipped - 10 lines]
>and the number of e's appearing in each
sentence?
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200703/1

Mar 5 '07 #4
MLH
Totally elegant
Mar 6 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by pembed2003 | last post: by
6 posts views Thread by Dale Atkin | last post: by
7 posts views Thread by sathyashrayan | last post: by
5 posts views Thread by andy.lee23 | last post: by
1 post views Thread by gisleyt | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by listenups61195 | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.