Android SQLite query where column is not null and not empty

android sqlite android-sqlite
cfrz picture cfrz · Jul 12, 2015 · Viewed 19.3k times · Source

I cannot figure out the syntax for the .query call. I need to select all records that match a certain column that do not have a null or empty value for a second (different) column

my best attempt:

Cursor cursor = mDatabase.query(DatabaseOpenHelper.TABLE_ROOMS, mAllColumns,
    DatabaseOpenHelper.KEY_ROOM_HOSPITAL_ID
    + " =? AND " + DatabaseOpenHelper.KEY_ISO + " IS NOT NULL OR NOT ?",
    new String[]{String.valueOf(hospitalId), ""}, null, null, null);

This is returning ALL records. If I use AND in place of OR, it returns records matching hospitalId, but ignores the NOT NULL OR NOT "" part.

Any tips? Should I use a rawQuery call?

Answer

BladeCoder picture BladeCoder · Jul 13, 2015

I believe the correct syntax would be:

AND key IS NOT NULL AND key != ""

where key is your column

Related questions

How to perform an SQLite query within an Android application?

I am trying to use this query upon my Android database, but it does not return any data. Am I missing something? SQLiteDatabase db = mDbHelper.getReadableDatabase(); String select = "Select _id, title, title_raw from search Where(title_raw like " + "'%…

Read More
Set default value of an integer column SQLite

I am creating an SQLite database in android. db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" + KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_NAME + " TEXT NOT NULL, " + KEY_WORKED + " INTEGER, " + KEY_NOTE + " INTEGER);"); Is it possible to set the default value of …

Read More
When is SQLiteOpenHelper onCreate() / onUpgrade() run?

I have created my tables in my SQLiteOpenHelper onCreate() but receive SQLiteException: no such table or SQLiteException: no such column errors. Why? NOTE: (This is the amalgamated summary of tens of similar questions every week. Attempting to provide a "canonical" …

Read More