Android Sqlite Concat Two Columns

I have made the concatenation so far, but in the results, it displays UncleSam. What I want to do is to put a space between the two columns so the result would be Uncle Sam. Th

Solution 1:

You can concat like this:

CursorlocalCursor=this.myDataBase.rawQuery("SELECT (KEY_FNAME  || ' ' || KEY_LNAME) AS fullname, KEY_ID, KEY_DIAGNOSIS, KEY_LASTFFUP FROM DB_TABLE");

Your concated full name will be in the cursor column 'fullname'.

In main activity:

String[] data = newString[]{ "fullname", DBHelper.KEY_DIAGNOSIS, DBHelper.KEY_LASTFFUP};

(You should probably assign a DBHelper constant for "fullname").

Solution 2:

Android Sqlite concat two columns

After a little conversation with author of this thread i suggest you to don't concat columns (you really don't need it) and concat Strings retrieved from Cursor. Your statement will be more human-readable and solution cleaner.

Explanation:

Generally is very useful and efficient approach to represent your table on application layer with objects which will represent tables. For example if you had table User, so create new class User and columns in table will be equal to properties in this class. This way is i guess pretty elegant (if someone else will see your code, he won't be confused and scared)

Finally you can simply concat fname and lname when you'll add them to ListAdapter

---

So i prefer this way:

List<User> users = newArrayList<User>();
Useru=null;
Stringquery="select * from Table";
Cursorc= db.rawQuery(query, null);
if (c != null && c.moveToFirst()) {
   do { 
      u = newUser();
      u.setFirstName(c.getString(c.getColumnIndex("fname")));
      u.setLastName(c.getString(c.getColumnIndex("lname")));
      ...
      users.add(u);
   } while (c.moveToNext());
}

And then somewhere in ListAdapter you can do:

textView.setText(u.getFirstname() + " " + u.getLastName());

Hope it helps.

Solution 3:

I finally got it working.

publicCursorgetAllPatients()
 {
Cursor localCursor = //  this.myDataBase.query(DB_TABLE, newString[] { 
                KEY_ID, KEY_FNAME + "|| ' ' ||" + KEY_LNAME, KEY_DIAGNOSIS, KEY_LASTFFUP }, null, null, null, null, null);
if (localCursor != null)
  localCursor.moveToFirst();
return localCursor;
}

and

Cursor cursor = dbHelper.getAllPatients();
String[] data = newString[]{
        DBHelper.KEY_FNAME + "|| ' ' ||" + DBHelper.KEY_LNAME, DBHelper.KEY_DIAGNOSIS, DBHelper.KEY_LASTFFUP};
// int[] to = newint[] {R.id.fullname, R.id.diagnosis, R.id.lastffup};

dataAdapter = new SimpleCursorAdapter(this, R.layout.custom_row, cursor, data, to, 0);