[AccessD] SQL to count delimiters in a string

Thank you
Jim Hale

-----Original Message-----
From: A.D.Tejpal [mailto:adtp at touchtelindia.net]
Sent: Wednesday, August 24, 2005 12:58 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] SQL to count delimiters in a string


Jim,

    Sample query given below should get you the count of specified delimiter
in field named fldText. T_Data is the name of table.

    It makes use of function Fn_CountDelimiters() given below.

Best wishes,
A.D.Tejpal
--------------

Select Query
(Count of specified delimiter in field fldText) 
=================================
SELECT T_Data.fldText, Fn_CountDelimiters([fldText],"|") AS Occurences  FROM
T_Data;
=================================

User-Defined Function
(Count of specified delimiter (TxtDelimiter) 
  in a given string (TxtMain))
=================================
Function Fn_CountDelimiters(ByVal TxtMain _
                                As String, ByVal TxtDelimiter _
                                As String) As Long
    Fn_CountDelimiters = UBound(Split(TxtMain, TxtDelimiter))
End Function
=================================

  ----- Original Message ----- 
  From: Hale, Jim 
  To: 'Accessd (E-mail) 
  Sent: Wednesday, August 24, 2005 21:21
  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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
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.