CODEWITHSUNDEEP
androidsqlsqliteandroid-sqlite
Edoardo Pessina
Edoardo Pessina

Reputation: 55

SQLite skip a word in query

I'm working with SQLite (and databases in general) for the first time and I have a problem with queries.

My database contains names of streets, like Via Cesare Correnti. To allow a user to search for a street by its name I'm using this method:

 public List<Street> saveQueryResult(String query) throws SQLException {
    query = query.replaceAll("'", "''");

    Cursor mCursor = mDataBase.query(
            TABLE_STREETS,
            ALL_COLUMN,
            KEY_NAME + " LIKE '%" + query + "%'",
            null,
            null,
            null,
            KEY_NAME + " ASC"
    );

    return createStreetsFromCursor(mCursor);
}

Considering for example "Via Cesare Correnti", if the users search for "Via Cesare" or "Cesare Correnti" everything works, but if he search "Via Correnti" (skipping a word) the result doesn't show up.

How can I modify my query to allow users to find streets even if they skip a word ?

Upvotes: 1

Views: 97

Answers (2)

vladaman
vladaman

Reputation: 3908

Use full text virtual table in sqlite. This allows you to perform better matching queries against text fields. Including white space.

CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT);

And then use MATCH

See https://www.sqlite.org/fts3.html

Upvotes: 1

Tung Tran
Tung Tran

Reputation: 2955

Try this:

Replace white blank character by %

query = query.trim();
query = query.replaceAll(" ", "%");

Example: "Via Correnti" to "%Via%Correnti%"; and

Cursor mCursor = mDataBase.query(
        TABLE_STREETS,
        ALL_COLUMN,
        KEY_NAME + " LIKE '%" + query + "%'",
        null,
        null,
        null,
        KEY_NAME + " ASC"
);

Upvotes: 1

Related Questions