473,324 Members | 2,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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 23402
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: pembed2003 | last post by:
Hi all, As an exercise, I am trying to come up with a function to count the length of a char* string using recursion. I came up with this: int count_length(char* s){ if(s == 0) return 0;...
6
by: Dale Atkin | last post by:
As part of a larger project, I need to be able to count the number of lines in a file (so I know what to expect). Anyways, I came accross the following code that seems to do the trick, the only...
4
by: sun6 | last post by:
this is a program counting words from "text_in.txt" file and writing them in "text_out.txt". it uses binary tree search, but there is an error when i use insert () thanks for any help ...
7
by: sathyashrayan | last post by:
Group, Following function will check weather a bit is set in the given variouble x. int bit_count(long x) { int n = 0; /* ** The loop will execute once for each bit of x set,
7
by: zets | last post by:
I need a macro for counting the bits in the odd positions of a given input (of any type, char, pointer, int, struct, whatever). Is there any clever way I could not think of, to do it efficiently? ...
5
by: andy.lee23 | last post by:
hi im having trouble counting lines in a text file, i have the following code int node1, node2, i; char name; float value; ifstream fin; fin.open(OpenDialog1->FileName.c_str()); i=1;
1
by: gisleyt | last post by:
I'd like to use the index-function to retrieve a certain char in a string, but i need it to start counting from the last char. E.g. in the string. Foo-bar, foo foo-bar I want the latter...
6
by: Matt Chwastek | last post by:
Anyone who can help, I am curretnly attempting to write some code that will allow iteration using a vector<intfrom the highest possilbe degree of a combination of ones & zeros (111, 110, 101,...
3
by: majna | last post by:
I have character counter for textarea wich counting the characters. Special character needs same place as two normal characters because of 16-bit encoding. Counter is counting -2 when special...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.