Android Question SQLite SOUNDEX

DonManfred

Expert
Licensed User
Longtime User
Upvote 0

KMatle

Expert
Licensed User
Longtime User
Here you go (only Alpha-tested)

B4X:
Sub SoundexEncode(word As String)
    Dim UsedLetters() As String = Array As String("B", "F", "P", "V","C","G", "J", "K", "Q", "S", "X", "Z","D", "T","L","M","N","R")
    Dim UC As Map
    UC.Initialize
    For i=0 To UsedLetters.Length-1
        If i<4 Then
            UC.Put(UsedLetters(i),1)
        End If
        If i>=4 And i<12 Then
            UC.Put(UsedLetters(i),2)
        End If
        If i>=12 And i<14 Then
            UC.Put(UsedLetters(i),3)
        End If
        If i=14 Then
            UC.Put(UsedLetters(i),4)
        End If
        If i>14 And i<17 Then
            UC.Put(UsedLetters(i),5)
        End If
        If i=17 Then
            UC.Put(UsedLetters(i),6)
        End If
        
    Next
    Dim Letter,Replacement As String
    
    Dim SoundexWord As String
    word=word.ToUpperCase
    For i=0 To word.Length -1
        Letter=word.SubString2(i,i+1)
            If UC.ContainsKey(Letter) Then
                If SoundexWord.Length=0 Then
                   'first letter 
                    SoundexWord=SoundexWord&Letter
                Else
                    Replacement=UC.Get(Letter)
                    If SoundexWord.SubString2(SoundexWord.Length-1,SoundexWord.Length)<>Replacement Then
                        SoundexWord=SoundexWord&UC.Get(word.SubString2(i,i+1))
                    End If
                End If
            End If
            If SoundexWord.Length=4 Then Exit           
        Next
    Do While SoundexWord.Length<4
       SoundexWord=SoundexWord&"0"   
    Loop
    
    Log("Soundex encoded: " & SoundexWord)
    
End Sub
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
I need it in the DB, otherwise I would have to read all the table data and filter it using your function.
Use SQLCipher:
B4X:
strQuery = $"Select Soundex(col2) AS sdx, col2, col3 FROM table1 "$
rs=SQL1cipher.ExecQuery(strQuery)
    Do While rs.NextRow
        Log(rs.GetString("col2") & "," & rs.GetString("sdx") & "," & rs.GetString("col3"))
    Loop
Here is the output:
mango,M520,rick
orange,O652,tom
banana,B550,steve
apple,A140,mark
dates,D320,sam
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
I'll try your suggestion anyway
When you get a chance to test it and if it works for you using SQLCipher like the test I conducted, let us know either way. Also, please let us know the reason for implementing this Soundex function which is not something you use regularly, and what you are trying to achieve by using it.
 
Last edited:
Upvote 0

LucaMs

Expert
Licensed User
Longtime User
When you get a chance to test it and if it works for you using SQLCipher like the test I conducted, let us know either way. Also, please let us know the reason for implementing this Soundex function and what you are trying to achieve by using it.
Yes, it worked perfectly, thanks.

The aim is to search a DB field for text detected by an OCR scan, which may be imperfect.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
I used... B4A

If you are also using the function you were given in post #6 that does it outside SQLite, you may want to check it again, because SQLite yields the soundex for Hello which is: H400. But the code in post #6 yields: E610
Of course you want to add more letters to the array of letters to include H and any others not in the array.
 
Upvote 0
Top