CODEWITHSUNDEEP
sqlms-accessvba
jzr
jzr

Reputation: 60

Counting characters in an Access database column using SQL

I have the following table

col1  col2  col3  col4
====  ====  ====  ====
1233  4566  ABCD  CDEF 
1233  4566  ACD1  CDEF
1233  4566  D1AF  CDEF

I need to count the characters in col3, so from the data in the previous table it would be:

char  count
====  =====
A         3
B         1
C         2
D         3
F         1
1         2

Is this possible to achieve by using SQL only?

At the moment I am thinking of passing a parameter in to SQL query and count the characters one by one and then sum, however I did not start the VBA part yet, and frankly wouldn't want to do that.

This is my query at the moment:

PARAMETERS X Long;
SELECT First(Mid(TABLE.col3,X,1)) AS [col3 Field], Count(Mid(TABLE.col3,X,1)) AS Dcount
FROM TEST
GROUP BY Mid(TABLE.col3,X,1)
HAVING (((Count(Mid([TABLE].[col3],[X],1)))>=1));

Ideas and help are much appreciated, as I don't usually work with Access and SQL.

Upvotes: 2

Views: 3902

Answers (4)

Zev Spitz
Zev Spitz

Reputation: 15317

It's a shame that you don't want to consider using VBA; you don't need as much as you might think:

Public charCounts As Dictionary

Sub LoadCounts(s As String)
    If charCounts Is Nothing Then Init
    Dim length As Integer, i As Variant
    length = Len(s)
    For i = 1 To length
        Dim currentChar As String
        currentChar = Mid(s, i, 1)
        If Not charCounts.Exists(currentChar) Then charCounts(currentChar) = 0
        charCounts(currentChar) = charCounts(currentChar) + 1
    Next
End Sub

Sub Init()
    Set charCounts = New Scripting.Dictionary
    charCounts.CompareMode = TextCompare 'for case-insensitive comparisons; otherwise use BinaryCompare
End Sub

Then, you execute the query once:

SELECT LoadCount(col3)
FROM Table1

Finally, you read out the values in the Dictionary:

Dim key As Variant
For Each key In charCounts
    Debug.Print key, charCounts(key)
Next

Note that between query executions you have to call Init to clear out the old values.

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123484

You can accomplish your task in pure Access SQL by using a Numbers table. In this case, the Numbers table must contain integer values from 1 to some number larger than the longest string of characters in your source data. In this example, the strings of characters to be processed are in [CharacterData]:

CharacterList
-------------
GORD         
WAS          
HERE

and the [Numbers] table is simply

 n
--
 1
 2
 3
 4
 5

If we use a cross join to extract the characters (eliminating any empty strings that result from n exceeding Len(CharacterList))...

SELECT 
    Mid(cd.CharacterList, nb.n, 1) AS c 
FROM 
    CharacterData cd, 
    Numbers nb
WHERE Mid(cd.CharacterList, nb.n, 1) <> ""

...we get ...

c
--
G
W
H
O
A
E
R
S
R
D
E

Now we can just wrap that in an aggregation query

SELECT c AS Character, COUNT(*) AS CountOfCharacter
FROM
    (
        SELECT 
            Mid(cd.CharacterList, nb.n, 1) AS c 
        FROM 
            CharacterData cd, 
            Numbers nb
        WHERE Mid(cd.CharacterList, nb.n, 1) <> ""
    )
GROUP BY c

which gives us

Character  CountOfCharacter
---------  ----------------
A                         1
D                         1
E                         2
G                         1
H                         1
O                         1
R                         2
S                         1
W                         1

Upvotes: 2

code save
code save

Reputation: 1106

Please Try this,,, I hope this will work

with cte  as
(
    select row_number() over(order by (select null)) as i from Charactor_Count
)

 select substring( name, i, 1 ) as char, count(*) as count
      from Charactor_Count, cte
     where cte.i <= len(Charactor_Count.name)
     group by substring(name,i,1)
     order by substring(name,i,1)

Upvotes: -1

wvdz
wvdz

Reputation: 16641

Knowing that colum3 has a fixed length of 4, this problem is quite easy.

Assume there is a view V with four columns, each for one character in column 3.

V(c1, c2, c3, c4)

Unfortunately, I'm not familiar with Access-specific SQL, but this is the general SQL statement you would need:

SELECT c, COUNT(*) FROM
(
SELECT c1 AS c FROM V
UNION ALL
SELECT c2 FROM V
UNION ALL
SELECT c3 FROM V
UNION ALL
SELECT c4 FROM V
)
GROUP BY c

Upvotes: 0

Related Questions