Count instaces of charcter in field

  • Thread starter Thread starter Barkster
  • Start date Start date
B

Barkster

I have a field that I want to count the number of times there is a
semicolon. I there anyway I can can do this in a query using sql? I
don't think it is possible but you never know. Thanks
 
Thanks that works but when I create that query and try and access it
through visual studio it shows up as a function and I'm unable to acess
it. Since Len & replace are functions of access and not sql. Do you
have any ideas of how to access it through visual studio?

Duane said:
Try
SemiColonCount: Len([YourField]) - Len(Replace([YourField],";",""))

--
Duane Hookom
MS Access MVP


Barkster said:
I have a field that I want to count the number of times there is a
semicolon. I there anyway I can can do this in a query using sql? I
don't think it is possible but you never know. Thanks
Click to expand...
Click to expand...
 
I missed any mention of visual studio in your original post ;-)
I'm not aware of a solution outside of the Access environment.

--
Duane Hookom
MS Access MVP

Barkster said:
Thanks that works but when I create that query and try and access it
through visual studio it shows up as a function and I'm unable to acess
it. Since Len & replace are functions of access and not sql. Do you
have any ideas of how to access it through visual studio?

Duane said:
Try
SemiColonCount: Len([YourField]) - Len(Replace([YourField],";",""))

--
Duane Hookom
MS Access MVP


Barkster said:
I have a field that I want to count the number of times there is a
semicolon. I there anyway I can can do this in a query using sql? I
don't think it is possible but you never know. Thanks
Click to expand...
Click to expand...
Click to expand...
 
The Len() function works in queries executed from external apps, including
..NET (I've tested it using C# and ADO.NET). But the Replace() function
doesn't. Unfortunately, I can't think of a way of doing it without using
Replace.

--
Brendan Reynolds
Access MVP

Barkster said:
Thanks that works but when I create that query and try and access it
through visual studio it shows up as a function and I'm unable to acess
it. Since Len & replace are functions of access and not sql. Do you
have any ideas of how to access it through visual studio?

Duane said:
Try
SemiColonCount: Len([YourField]) - Len(Replace([YourField],";",""))

--
Duane Hookom
MS Access MVP


Barkster said:
I have a field that I want to count the number of times there is a
semicolon. I there anyway I can can do this in a query using sql? I
don't think it is possible but you never know. Thanks
Click to expand...
Click to expand...
Click to expand...
 
Ok thanks, I'll see if I can do it another way. Appreciate the
comments.

Brendan said:
The Len() function works in queries executed from external apps, including
.NET (I've tested it using C# and ADO.NET). But the Replace() function
doesn't. Unfortunately, I can't think of a way of doing it without using
Replace.

--
Brendan Reynolds
Access MVP

Barkster said:
Thanks that works but when I create that query and try and access it
through visual studio it shows up as a function and I'm unable to acess
it. Since Len & replace are functions of access and not sql. Do you
have any ideas of how to access it through visual studio?

Duane said:
Try
SemiColonCount: Len([YourField]) - Len(Replace([YourField],";",""))

--
Duane Hookom
MS Access MVP


I have a field that I want to count the number of times there is a
semicolon. I there anyway I can can do this in a query using sql? I
don't think it is possible but you never know. Thanks
Click to expand...
Click to expand...
Click to expand...
 
Barkster said:
I have a field that I want to count the number of times there is a
semicolon. I there anyway I can can do this in a query using sql?
Click to expand...

Here are a couple of postings from the archives that use the standard
trick of a Sequence table of integers (and nothing from the Access UI)
to parse text:

http://groups.google.com/group/microsoft.public.access.modulesdaovba/msg/572c81477aea0d60

http://groups.google.com/group/microsoft.public.access/msg/ec314002d0c63764

The VBA code is merely to reproduce the test databases. The SQL is Jet
SQL, therefore such an approach should work from your project.

Jamie.

--
 
Thanks for the post, I may end up just adding a field and updating it
using this function for the count then update my original program to
count them before inserting into the database. Might be better for me
that looks a little complicated for me. Thanks!
 
Thanks for the post

that looks a little complicated for me.
Click to expand...

Actually, it's quite straightforward.

Let's use a simple example, using Northwind.

I can't think of anything in Northwind with semicolon characters, so
lets say the challenge is to count the number of letter a's in
employees' last names.

First, we need a Sequence table of integers. Northwind doesn't have
such a base table so we can create a derived table:

SELECT Sequence.seq
FROM (
SELECT (
SELECT COUNT(*)
FROM Customers AS C2
WHERE C1.CustomerID >= C2.CustomerID
) AS seq
FROM Customers AS C1
) AS Sequence;

We can use the Sequence table to parse out each character in
Employees.LastName:

SELECT E1.LastName, Sequence.seq,
MID(E1.LastName, Sequence.seq, 1) AS char_parsed
FROM Employees AS E1, (
SELECT (
SELECT COUNT(*)
FROM Customers AS C2
WHERE C1.CustomerID >= C2.CustomerID
) AS seq
FROM Customers AS C1
) AS Sequence
WHERE Sequence.seq BETWEEN 1 AND LEN(E1.LastName)
ORDER BY E1.LastName, Sequence.seq;

We can add a column to determine whether the parsed character is an
'a':

SELECT E1.LastName, Sequence.seq,
MID(E1.LastName, Sequence.seq, 1) AS char_parsed,
IIF(MID(E1.LastName, Sequence.seq, 1) = 'a', 1, 0) AS is_a
FROM Employees AS E1, (
SELECT (
SELECT COUNT(*)
FROM Customers AS C2
WHERE C1.CustomerID >= C2.CustomerID
) AS seq
FROM Customers AS C1
) AS Sequence
WHERE Sequence.seq BETWEEN 1 AND LEN(E1.LastName)
ORDER BY E1.LastName, Sequence.seq;

Finally, we can GROUP and SUM:

SELECT E1.LastName, SUM(IIF(MID(E1.LastName, Sequence.seq, 1) = 'a', 1,
0)) AS tally_a
FROM Employees AS E1, (
SELECT (
SELECT COUNT(*)
FROM Customers AS C2
WHERE C1.CustomerID >= C2.CustomerID
) AS seq
FROM Customers AS C1
) AS Sequence
WHERE Sequence.seq BETWEEN 1 AND LEN(E1.LastName)
GROUP BY E1.LastName;

HTH,
Jamie.

--
 
Jamie said:
we need a Sequence table of integers. Northwind doesn't have
such a base table so we can create a derived table
Click to expand...

I missed a trick: I could have created a Sequence base table

SELECT Sequence.seq
INTO Sequence
FROM (
SELECT (
SELECT COUNT(*)
FROM Customers AS C2
WHERE C1.CustomerID >= C2.CustomerID
) AS seq
FROM Customers AS C1
) AS Sequence;

Then the subsequent queries would have looked simpler e.g.

SELECT E1.LastName,
SUM(IIF(MID(E1.LastName, S1.seq, 1) = 'a', 1, 0)) AS tally_a
FROM Employees AS E1, Sequence AS S1
WHERE S1.seq BETWEEN 1 AND LEN(E1.LastName)
GROUP BY E1.LastName;

Jamie.

--
 
Back
Top