Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2002
    Posts
    63

    Talking Unanswered: count occurence of string within a string.

    Hi, Ive looked everywhere for this but can't find it, so i'm comming to u lot, does some praying at the feet of dbforums.

    I want to count the number of times a string occurs within another string e.g.

    "hello there" looking for the string "e" would return "3".

    Anyone know of a function for this???

    Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    I ran into the same problem myself, mythix. I ended up writing a function. It returns the count of a specified character in a string. Pass it the string to search, and the character to search for.

    ?CountCharacterInString("hello there", "e")
    3
    Code:
    Function CountCharacterInString(strToSearch As String, strCharToCount As String) As Long
       Dim lPos As Long
       Dim lTotal As Long
       For lPos = 1 To Len(strToSearch)
          If Mid$(strToSearch, lPos, Len(strCharToCount)) = strCharToCount Then
             lTotal = lTotal + 1
          End If
       Next
       CountCharacterInString = lTotal
    End Function

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't have access 2000 to test this on, but according to the docs, you can use the REPLACE function in access 2000

    select len(thefield)
    - len(replace(thefield,'3',''))
    as numberofthrees
    from yourtable


    rudy

  4. #4
    Join Date
    Jun 2002
    Posts
    63
    Thanks :::does some general grinning:::

    If anyones interested I managed to find a handy load of string functions:

    http://www.romankoch.ch/capslock/strfun.htm

    By the way it is in english despite being .ch

  5. #5
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179

    Smile

    Nice smile mythix!

    Nice find too - thanks for passing on those string functions.

    Also - r937's solution works only if the string you're searching for is one character. If the string you're searching more than one character, the result won't be accurate.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    true, but you could then use

    select
    ( len(thefield) - len(replace(thefield,'foo','')) ) / len('foo')

    and substitute whatever field you want instead of 'foo'

    i left that part out because len('3') = 1




  7. #7
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    cooooo
    That should take care of any lurkers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •