CODEWITHSUNDEEP
androidregexsqlite
user1800825
user1800825

Reputation: 203

query sqllite database by the first letter of an entry

I have created a TabLayout and I want to display the entries of my database by the selected tab. Here is the code snippet with the query: 

public Cursor filteredItems(int tabPosition) throws SQLException {
        Log.d(TAG, "filteredItems called, Parameter: "+tabPosition); 

        String filter = ""; 
        if(tabPosition == 0){
            filter = "[a-cA-C]%";
        }else if(tabPosition == 1){
            filter = "[d-fD-F]%";
        }else{
            Log.d(TAG, "default tab"); 
            filter = "[a-zA-Z]%";
        }

        Cursor cursor = null;
        try{
            open();
            String query ="SELECT _id, surname, prename, street, zipcode, telephone, email FROM "+Database.getMyDbTable()+" " +
                    "WHERE surname LIKE '"+filter+"';";
            Log.d(TAG,"Query "+query);
            cursor = database.rawQuery(query, null);
        }catch(Exception ex){
            Log.e(TAG, "Could not get list");
            CharSequence text = dataNotFound;
            Toast toast = Toast.makeText(context, text, duration);
            toast.show();
        }

        return cursor;
    }

Unfortunately, the filter does not work - the result of the query is always empty(the table is not empty - I have checked it).

Upvotes: 0

Views: 1454

Answers (4)

CL.
CL.

Reputation: 180020

LIKE cannot match with character sets.

Since you care only for the first letter, you can use BETWEEN instead:

SELECT ... FROM MyTable WHERE surname BETWEEN 'A' AND 'Czzzz'

To handle both upper and lower case, either use two conditions, or change the collation:

SELECT ... FROM MyTable WHERE surname BETWEEN 'A' AND 'Czzzz'
                           OR surname BETWEEN 'a' AND 'czzzz'

SELECT ... FROM MyTable WHERE surname COLLATION UNICODE BETWEEN 'A' AND 'Czzzz'

The latter can use an index only if the column or index has been declared with the same collation.

Upvotes: 0

Rajesh
Rajesh

Reputation: 15774

First, the LIKE operator does not take regular expressions. The REGEXP operator that does this is, unfortunately, not supported in Android.

Solution: use LIKE with logical OR.

String query ="SELECT _id, surname, prename, street, zipcode, telephone, email FROM "+Database.getMyDbTable()+" WHERE surname LIKE 'a%' OR surname LIKE 'b%' OR surname LIKE 'c%';";

Upvotes: 1

Raghav Sood
Raghav Sood

Reputation: 82543

Try something like: 

"SELECT _id, surname, prename, street, zipcode, telephone, email FROM " + Database.getMyDbTable() + " WHERE substr(" + Database.getMyDbTable()  +",1) LIKE " + <yourletter>

By using substr, you're comparing only the first letter.

Upvotes: 0

Omar
Omar

Reputation: 463

You need to use wildcards for this purpose. Read at http://sqlite.awardspace.info/syntax/sqlitepg03.htm

Try

SELECT _id, surname, prename, street, zipcode, telephone, email FROM table WHERE surname LIKE '<your letter(s)>%'

Upvotes: 1

Related Questions