CODEWITHSUNDEEP
sqliteandroid-room
Big O
Big O

Reputation: 417

A Search Query Term Should Not Be Prefix of Any Data in Db

Give a table User_DNA and column a sequence, I want to write a query such that if data in the sequence column matches or is prefix of the search query term, it should return an item found or true. e.g,

sequence

dog

hor

cat

tig

cat

if my search query is doga (has dog as prefix in db), horrible(has hor as prefix in db),tiger(has tig as prefix in db), caterpillar(has cat as prefix in db), the query should return true as all these search queries have prefixes in the database.

What should my sql search query?

Thanks

Upvotes: 0

Views: 79

Answers (1)

sergiy tykhonov
sergiy tykhonov

Reputation: 5103

If you use Room, you can try this (using Kotlin):

@Query("select * from User_DNA WHERE sequence LIKE :search")
fun getItem(search: String): UserDNA?

Setting your search parameter to this method you should put the search pattern as "[your search string]%", for example: "dog%"

if my search query is dog, the query should return true or one of the items (dogsequence/doggysequence) what ever is efficient

You can check result of the query - if it's null, then there are no matching values in your column.

UPDATED

If you want to find "hor" with "horrible" I can propose next way (maybe it's a task for RegExp but honestly I haven't used it in ROOM):

  1. You can put two methods in your DAO. One method is auxiliary, its task - is ti fill a list with words, that we want to find. For example, for pattern "horrible" that method should prepare list with {"horrible", "horribl", "horrib", "horri", "horr", "hor"}.
  2. Second method should fetch a result from SQLite where your fields holds value from the list prepared on step1. This method should be annotated with Room annotation.
  3. So first method prepares list, invokes query for searching word in SQLite and returns it to ViewModel (or Repository).

Something like this:

@Query("select * from User_DNA WHERE sequence IN (:search) ORDER BY sequence")
fun getItem(search: List<String>): User_DNA?

fun findItem(search: String): User_DNA? {
    val searchList = mutableListOf<String>()
    val minimalStringLength = 2 // it's up to you, maybe 1?
    while (search.length > minimalStringLength) {
        searchList.add(search)
        search = search.dropLast(1)
    }
    return getItem(searchList.toList())
}

Upvotes: 1

Related Questions