Android Question SQLite SOUNDEX

[LucaMs]

Is there any way to activate SOUNDEX in SQLite?

android.database.sqlite.SQLiteException: no such function: SOUNDEX (Sqlite code 1): , while compiling:...

https://www.sqlite.org/compile.html#soundex

 

[DonManfred]

Compile-time Options

This function is omitted from SQLite by default. It is only available if the SQLITE_SOUNDEX compile-time option is used when SQLite is built.

 

[LucaMs]

Yes, I think but I'm not sure if Erel can do this in a new version of the SQL library.

 

[DonManfred]

if Erel can do this in a new version of the SQL library.

SQL Library is using the already installed SQLite. I don´t think a new Version also installs a new SQLite on Android.

 

[KMatle]

Looks quite easy to adapt (only a few lines to code).

 

[KMatle]

Here you go (only Alpha-tested)

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

 

[LucaMs]

Looks quite easy to adapt (only a few lines to code).

Looks quite easy to adapt (only a few lines to code).

Thanks, Klaus, but obviously I need it in the DB, otherwise I would have to read all the table data and filter it using your function.

 

[Mahares]

I need it in the DB, otherwise I would have to read all the table data and filter it using your function.

Use SQLCipher:

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

 

[LucaMs]

Use SQLCipher

I'm almost done with my app but I think I'll try your suggestion anyway, @Mahares, thanks.

"Small" app that I developed in about a dozen hours but only because I used... B4A !!!

 

[Mahares]

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.

 

[LucaMs]

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.

 

[Mahares]

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.