searching a particular string in the entire database using Sqlite3?

Question

I want to search a particular string in the whole database table and get the row details of the matched column. For example I am having the table as like below

  **name  email   address  designation**
    sai   xy@mail  75385      nagar
    thiru  y@mail  75893      city

Now I am having the searching key like "nagar" or "thiru" how can prepare a query for it. if any one knows Please help me. Thanks in advance.

Please tell me.You need search a particular string in particular Column. — user1423506
– user1423506, Commented Aug 9, 2012 at 12:22

Seshu Vinay · Accepted Answer · 2012-08-09 12:40:39Z

Read the whole table into a cursor and search in each column to get column name.

May be like this:

private String getColumnName(String key){
     String columnName="";
     DataBaseAdapter dba=new DatabaseAdapter(getApplicationContext());
     dba.open();
     Cursor cr=dba.fetchAllData();
     cr.moveToFirst();
     while(!cr.isAfterLast()){
            if(key.equals(cr.getString(cr.getColumnIndex("name")))){
                   columnName="name";
            }
            cr.moveToNext();
     }
     cr.moveToFirst();
     while(!cr.isAfterLast()){
            if(key.equals(cr.getString(cr.getColumnIndex("email")))){
                   columnName="email";
            }
            cr.moveToNext();
     }
     cr.moveToFirst();
     while(!cr.isAfterLast()){
            if(key.equals(cr.getString(cr.getColumnIndex("address")))){
                   columnName="address";
            }
            cr.moveToNext();
     }
     cr.moveToFirst();
     while(!cr.isAfterLast()){
            if(key.equals(cr.getString(cr.getColumnIndex("designation")))){
                   columnName="designation";
            }
            cr.moveToNext();
      }
       return columnName;
    }

fetchAllData() function in DataBaseAdapter class contains following code:

public Cursor fetchAllData(){

  try {
   return database.query(TABLENAME, null, null, null, null,
     null, null);
  } catch (Exception e) {
   return null;
  }
}

This will give column name of your key.

So now it's easy to run query like this:

 String query=  "SELECT * FROM tablename where "+getColumnName()+"='"+key+"'" ;

jnthnjns · Accepted Answer · 2012-08-09 12:28:49Z

1

try this query..

SELECT * FROM tablename WHERE name = 'nagar' or email = 'nagar' or address = 'nagar' or designation = 'nagar';

or

SELECT * FROM tablename WHERE name or email or address or designation = 'nagar';

edited Aug 9, 2012 at 12:28

jnthnjns

8,9254 gold badges44 silver badges70 bronze badges

answered Aug 9, 2012 at 12:17

Manoj Manani

5074 silver badges9 bronze badges

1 Comment

Thiru Over a year ago

Thanks for your valuable answer. This will work fine when I am having the full value (ie) for example name="thiru" but I am searching with the key "th" only. I have to find the value is present in which column and in which row. How to code in that scenario?

Collectives™ on Stack Overflow

searching a particular string in the entire database using Sqlite3?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related