[AccessD] SQL to count delimiters in a string

Hi Jim

Here's the real simple method:

  SELECT
    Len([fldText])-Len(Replace([fldText], '|', '')) AS Occurrences
  FROM ...

If you expect Null values use:
    Len([fldText])-Len(Replace('' & [fldText], '|', '')) AS
Occurrences
to prevent errors.

If you want a count of zero even for Null values:
    Len('' & [fldText])-Len(Replace('' & [fldText], '|', '')) AS
Occurrences

For Access 97 and below you'll need to supply the Replace() function
too.

/gustav


>>> Jim.Hale at FleetPride.com 24-08-2005 17:51 >>>
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