Enable Soundex in Sqlite

Question

I want to use soundex with sqlite.The goal is for homophones to be encoded to the same representation so that they can be matched despite minor differences in spelling.

I am using below code-

class SearchDBManager private constructor(context: Context) : BaseSQLiteOpenHelper(context, SQLITE_FILE_NAME, null, DATABASE_VERSION) {

    override fun onCreate(db: SQLiteDatabase?) {
        db?.execSQL(CREATE_SEARCH_TABLE)
    }

    override fun onUpgrade(db: SQLiteDatabase?, p1: Int, p2: Int) {
    }

    fun insertSearchList(station: HashMap<String, String>) {
        var db: SQLiteDatabase? = null
        try {
            db = writableDatabase
        } catch (e: IllegalStateException) {
            CrashLogger.logException(e)
        } catch (e: SQLException) {
            CrashLogger.logException(e)
        }
        if (db == null) {
            return
        }
        try {


            for ((key, value) in station) {
                val values = ContentValues()
                values.put(COLUMN_CODE, key)
                values.put(COLUMN_NAME, value)
                values.put(COLUMN_SOUNDEX, "soundex("+value+")")

                LogcatLogger.d(TAG, key + " " + value + " " + System.currentTimeMillis())
                insert(TABLE_SEARCH, null, values)

            }



        } catch (e: SQLException) {
            CrashLogger.logException(e)
        }

    }


    companion object {
        val DATABASE_VERSION = 1
        val SQLITE_FILE_NAME = "Search.sqlite"
        private val TAG = "SearchDBManager"
        private val TABLE_SEARCH = "Search"
        private val COLUMN_CODE = "Code"
        private val COLUMN_NAME = "Name"
        private val COLUMN_SOUNDEX = "SSoundex"

        private var sInstance: SearchDBManager? = null
        private val COMMA_SEP = ","

        private val CREATE_SEARCH_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_SEARCH + " (" +
                COLUMN_CODE + " TEXT NOT NULL UNIQUE " + COMMA_SEP +
                COLUMN_NAME + " TEXT " + COMMA_SEP +
                COLUMN_SOUNDEX + " TEXT " + ")"

        val instance: SearchDBManager?
            @Synchronized get() {
                if (sInstance == null) {
                    sInstance = SearchDBManager(XYApplication.getInstance())
                }
                return sInstance
            }
    }


}

Above code inserts value in table as soundex(<value>) while i want it to encode the value.I researched on net and found- How to use Soundex with SQLITE in android but I don't understand how to use SQLITE_SOUNDEX compile-time option. How can i enable soundex in sqlite?

You might try using `execSQL()` with an `INSERT` statement rather than `insert()`. If that does not help, then perhaps the framework SQLite does not have soundex compiled in. If so, then you would either need to package your own independent copy of SQLite with your app or just implement a soundex algorithm in your app code. — CommonsWare, May 25 '19 at 12:35
@CommonsWare But as per [doc](https://www.sqlite.org/lang_corefunc.html#soundex_) sqlite has soundex function and we can enable it using SQLITE_SOUNDEX compile-time option.How can i use SQLITE_SOUNDEX compile-time option? — Android Developer, May 25 '19 at 12:39
You would need to package your own independently-compiled copy of SQLite with your app, where you enabled the `SQLITE_SOUNDEX` option. Or, you would need to find an existing independently-compiled copy of SQLite that has it enabled already (Requery's perhaps, or maybe SQLCipher for Android). Or, you would need to build your own custom Android ROM, where you package in it a copy of SQLite that has `SQLITE_SOUNDEX`. — CommonsWare, May 25 '19 at 12:59

Enable Soundex in Sqlite

0 Answers0