[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