[AccessD] SQL to count delimiters in a string

Yu need to use a VBA Function to do that, which you would call from the
query. Here's an example...

Function CountDelimiters(strSource As String, strDelim As String) As Long
Dim nCount As Long
Dim nPos As Long
    nPos = InStr(strSource, strDelim)
    While nPos > 0
        nCount = nCount + 1
        nPos = InStr(nPos + Len(strDelim), strSource, strDelim)
    Wend
    CountDelimiters = nCount
End Function

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
Sent: Wednesday, August 24, 2005 11:52 AM
To: 'Accessd (E-mail)
Subject: [AccessD] SQL to count delimiters in a string


Assume I have a text field in a table. Further assume that the text strings
are delimited by the pipe character "|". I want to know how many pipe
characters occur in each field, ie. the select statement should return:

fldText                         Occurrences
test|ddd|ww|ex                3
fff|sss                             1
eenie|meenie|minie|moe   3

What is the correct SQL to produce the desired result? TIA
Jim Hale

***********************************************************************
The information transmitted is intended solely for the individual or entity
to which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of or
taking action in reliance upon this information by persons or entities other
than the intended recipient is prohibited. If you have received this email
in error please contact the sender and delete the material from any
computer. As a recipient of this email, you are responsible for screening
its contents and the contents of any attachments for the presence of
viruses. No liability is accepted for any damages caused by any virus
transmitted by this email.