Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Location
    Bangor, ME USA
    Posts
    44

    Question Unanswered: want to build soundex for customer IDs

    Hi all;

    for those who do not know what soundex is, it is used in some states i.e. FL for drivers license numbers. the way it is described in my book is this:

    The sounex system assigns license number groups with similar sounding letters and assigns a number to each group.

    1) Take the first letter of the last name (in my case could be a company name)

    2) add 3 digits by finding the number equivalent for each subsequen NON-doubled consonent. If the first letter is followed by a letter with the same number equivalent , goto the next consonant. If two letters withthe same number equivalent are separated by a "w" or "h", do not code the following letter.

    3) When there are insufficient consonants to get 3 numbers, one or more 0 (zeros) are added.

    4) After the initial letter, and 3 numbers, the coding systems vary from user to user.
    Given information for demo was Moran which should translate to M650

    for my function I want a format of Soundexname-STate_last3 of zip-a random number Like: M650-MI-558-45 or something to tyhat effect so that no 2 aree identical (May end up being PK but not sure yet).

    I have done preliminary testing using immediate window and get nothing but errors.

    my function call is x = assign_id("dentici","ME","04401") which should come out as D432-ME-401 the way I have it now. The case seect shows the letter groupings and numeric value.

    Code:
    Function Assign_ID(name As String, state As String, zip As String) As String
    On Error GoTo assign_error
    
    ' assigns a customer ID based on Soundex last name , state, zip and an incrementor so no 2 ID are the same
    
    Dim i As Integer ' for looping through the name
    Dim j As Integer 'for getting the 3 additional numbers
    Dim buildstring As String to get the assign_id built
    
     j = 1  
    buildstring = ""
     
     buildstring = Left(name, 1)
     
     For i = 2 To Len(name)
       Select Case Mid(name, i, 1)
         Case "b", "p", "f", "v"
          buildstring = buildstring & "1"
          j = j + 1
         Case "c", "g", "j", "k", "q", "s", "x", "z"
          buildstring = buildstring & "2"
          j = j + 1
         Case "d", "t"
          buildstring = buildstring & "3"
          j = j + 1
         Case "l"
          buildstring = buildstring & "4"
          j = j + 1
         Case "m", "n"
          buildstring = buildstring & "5"
         Case "r"
          buildstring = buildstring & "6"
          j = j + 1
      End Select
      Next
      
      Do While j < 4
        buildstring = buildstring & "0" 'If not enough letters this adds the 0's
        j = j + 1
      Loop
      
      
      buildstring = buildstring & "-" & state & "-" & Right(zip, 3) 'This will add the additional info I want but I need to add the random number to the end
     
    assign_error:
    MsgBox Err.Number & " - " & Err.Description, vbMsgBoxHelpButton, "Error_Logic Triggered"
    Resume exit_assign
    
    
    exit_assign:
    End Function
    any help or inspiration would be greatly appreciated.

    TIA
    Kevin

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Here is a soundex function that may help.

    '**************************************
    ' Name: ConvertToSoundex
    ' Description:Converts a name or word st
    ' ring to a four digit code following Soun
    ' dex rules.
    'Similar code is used by geniological groups and the US Census Bureau for
    'looking up names by phonetic sound. For example, the name Darrell can
    'be spelled many different ways. Regardles of how you spell it, (Daryl, Derrel,
    'Darel, etc.) the Soundex code is always D640. Therefore, you assign a field
    'in your database to the Soundex code and then query the database using
    'the code, all instances of Darrell regarless of spelling will be returned. Refer
    'to the code comment section for more information.
    ' By: Darrell Sparti
    '
    '
    ' Inputs:A single name or word string.
    '
    ' Returns:A four digit alphanumeric Soun
    ' dex code.
    '
    'Assumes:None
    '
    'Side Effects:This code has not been com
    ' mercially tested.
    'This code is copyrighted and has limite
    ' d warranties.
    'Please see http://www.Planet-Source-Cod
    ' e.com/xq/ASP/txtCodeId.1061/lngWId.1/qx/
    ' vb/scripts/ShowCode.htm
    'for details.
    '**************************************

    '***************************************
    ' ********************************
    'Function Name:ConvertToSoundex
    'Argument:A single name or word string
    'Return value:A 4 character code based o
    ' n Soundex rules
    'Author:Darrell Sparti
    'EMail:dsparti@allwest.net
    'Date: 9-20-98
    'Description:All Soundex codes have 4 al
    ' phanumeric
    ' characters, no more and no less, regar
    ' dless
    ' of the length of the string. The first
    '
    ' character is a letter and the other 3
    ' are
    ' numbers. The first letter of the strin
    ' g is
    ' the first letter of the Soundex code.
    ' The
    ' 3 digits are defined sequentially from
    ' the
    ' string using the following key:
    ' 1 = bpfv
    ' 2 = cskgjqxz
    ' 3 = dt
    ' 4 = l
    ' 5 = mn
    ' 6 = r
    ' No Code = aehiouyw
    ' If the end of the string is reached be


    ' fore
    ' filling in 3 numbers, 0's complete the
    ' code.
    ' Example: Swartz= S632
    ' Example: Darrell= D640
    ' Example: Schultz = S432
    'NOTE:I have noticed some errors in othe
    ' r versions
    'of soundex code. Most noticably is the
    'fact that not only must the code ignore
    '
    'the second letter in repeating letters
    '(ll,rr,tt,etc. for example), it must al
    ' so
    'ignore letters next to one another with
    ' the
    'same soundex code (s and c for example)
    ' .
    'Other wise, in the example above, Schul
    ' tz
    'would return a value of S243 which is
    'incorrect.
    '***************************************
    ' *****************************



    Public Function ConvertToSoundex(sInString As String) As String
    Dim sSoundexCode As String
    Dim sCurrentCharacter As String
    Dim sPreviousCharacter As String
    Dim iCharacterCount As Integer
    'Convert the string to upper case letter
    ' s and remove spaces
    sInString = UCase$(Trim(sInString))
    'The soundex code will start with the fi
    ' rst character _
    of the string
    sSoundexCode = Left(sInString, 1)
    'Check the other characters starting at
    ' the second character
    iCharacterCount = 2
    'Continue the conversion until the sound
    ' ex code is 4 _
    characters long regarless of the length of the string


    Do While Not Len(sSoundexCode) = 4

    'If the previous character has the same
    ' soundex code as _
    current character or the previous character is the same _
    as the current character, ignor it and move onto the next

    sCurrentCharacter = Mid$(sInString, iCharacterCount, 1)
    sPreviousCharacter = Mid$(sInString, iCharacterCount - 1, 1)



    If sCurrentCharacter = sPreviousCharacter Then
    iCharacterCount = iCharacterCount + 1
    ElseIf InStr("BFPV", sCurrentCharacter) Then


    If InStr("BFPV", sPreviousCharacter) Then
    iCharacterCount = iCharacterCount + 1
    End If
    ElseIf InStr("CGJKQSXZ", sCurrentCharacter) Then


    If InStr("CGJKQSXZ", sPreviousCharacter) Then
    iCharacterCount = iCharacterCount + 1
    End If
    ElseIf InStr("DT", sCurrentCharacter) Then


    If InStr("DT", sPreviousCharacter) Then
    iCharacterCount = iCharacterCount + 1
    End If
    ElseIf InStr("MN", sCurrentCharacter) Then


    If InStr("MN", sPreviousCharacter) Then
    iCharacterCount = iCharacterCount + 1
    End If
    Else
    End If

    'If the end of the string is reached bef
    ' ore there are 4 _
    characters in the soundex code, add 0 until there are _
    a total of 4 characters in the code


    If iCharacterCount > Len(sInString) Then
    sSoundexCode = sSoundexCode & "0"

    'Otherwise, concatenate a number to the
    ' soundex code _
    base on soundex rules
    Else
    sCurrentCharacter = Mid$(sInString, iCharacterCount, 1)


    If InStr("BFPV", sCurrentCharacter) Then
    sSoundexCode = sSoundexCode & "1"
    ElseIf InStr("CGJKQSXZ", sCurrentCharacter) Then
    sSoundexCode = sSoundexCode & "2"
    ElseIf InStr("DT", sCurrentCharacter) Then
    sSoundexCode = sSoundexCode & "3"
    ElseIf InStr("L", sCurrentCharacter) Then
    sSoundexCode = sSoundexCode & "4"
    ElseIf InStr("MN", sCurrentCharacter) Then
    sSoundexCode = sSoundexCode & "5"
    ElseIf InStr("R", sCurrentCharacter) Then
    sSoundexCode = sSoundexCode & "6"
    Else
    End If
    End If

    'Check the next letter
    iCharacterCount = iCharacterCount + 1
    Loop
    'Return the soundex code for the string
    ConvertToSoundex = sSoundexCode
    End Function
    Last edited by loquin; 04-10-06 at 16:19.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try pronouncing:
    'Jesús' in Spanish
    'hebdomadaire' or 'hôtel' in French
    'wunder' or 'zeit' or 'jäger' in German
    ...to a bunch of folk who dont know Spanish/French/German and see which first character they come up with.

    so i don't like soundex' first-letter-must-be-right-so-keep-it assumption.

    an example of a modified soundex is here - and yes, it returns false positives.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    izyrider, I agree that soundes isn't perfect. Pfieffer sure SOUNDS like it starts with "F", for instance. There have been many improvements published since soundex' initial design, over 80 years ago to address many of these issues.

    But, if you're trying to match an existing system that uses soundex, you'll certainly need to use the same algorithm, warts and all. If you're designing a new application, you aren't tied to soundex, so you could implement the improvements, either yours, or others.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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