[AccessD] SQL to count delimiters in a string

Jim,

It can be done in one line - so it should be possible to do it in MS Access
SQL I think.

Inline test in immediate window:

?IIf(Len("Just|a|test") > 0, UBound(Split("Just|a|test", "|")), 0)
2

One of the possible solutions as VBA function (lines wrapped
this tricky way to let this code copy from this message and to paste
it into VBA module and compile it without errors):

Public Function CountDelimiters( _
                               ByVal vstrSource As String, _
                               ByVal vstrDelimiter As String) As Long
    CountDelimiters = IIf(Len(vstrSource) > 0, _
                                    UBound(Split(vstrSource, _
                                    vstrDelimiter)), 0)
End Function

HTH,
Shamil

> -----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.
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com