Android Sqlite concat two columns

Question

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.

This is the code I'm working on:

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

from DBHelper.java

and

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

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

from my MainActivity.

Any help will do. Thanks.

Add an extra " " between the name and the surname you are retrieving? — ClaireG
– ClaireG, Commented Aug 6, 2013 at 15:49
@Sajmon to be displayed as one, but I need to put a space between them — Dunkey
– Dunkey, Commented Aug 6, 2013 at 16:24
you want to print data from cursor like Uncle Sam where Uncle is value from one column and Sam from another? — Simon Dorociak
– Simon Dorociak, Commented Aug 6, 2013 at 16:25

VM4 · Accepted Answer · 2013-08-06 16:50:53Z

3

You can concat like this:

Cursor localCursor = 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 = new String[]{ "fullname", DBHelper.KEY_DIAGNOSIS, DBHelper.KEY_LASTFFUP};

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

edited Aug 6, 2013 at 16:50

answered Aug 6, 2013 at 16:31

VM4

6,4995 gold badges40 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dunkey Over a year ago

But how about on my MainActivity?

VM4 Over a year ago

Simple: String[] data = new String[]{ "fullname", DBHelper.KEY_DIAGNOSIS, DBHelper.KEY_LASTFFUP};

user456118 Over a year ago

I think it would be something like this SELECT KEY_FNAME || ' ' || KEY_LNAME as fullname, KEY_ID, KEY_DIAGNOSIS, KEY_LASTFFUP FROM DB_TABLE. You can you Pipe to concatenate two columns in SQLite

VM4 Over a year ago

Yeah you're probably right, not sure if this will work on SQLite. Will make the edit.

Simon Dorociak · Accepted Answer · 2013-08-06 16:46:42Z

0

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 = new ArrayList<User>();
User u = null;
String query = "select * from Table";
Cursor c = db.rawQuery(query, null);
if (c != null && c.moveToFirst()) {
   do { 
      u = new User();
      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.

answered Aug 6, 2013 at 16:46

Simon Dorociak

33.5k10 gold badges70 silver badges107 bronze badges

2 Comments

VM4 Over a year ago

I kinda dissagree that the solution is cleaner. Selecting exactly what you need is alot cleaner than an unnecessary loop through data. The point of SimpleCursorAdapter is to avoid these loops.

Simon Dorociak Over a year ago

@VM his is only theory. In real applications is usually used this approach.

Dunkey · Accepted Answer · 2013-08-06 17:05:33Z

0

I finally got it working.

 public Cursor getAllPatients()
 {
Cursor localCursor = //  
        this.myDataBase.query(DB_TABLE, new String[] { 
                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 = new String[]{
        DBHelper.KEY_FNAME + "|| ' ' ||" + DBHelper.KEY_LNAME, DBHelper.KEY_DIAGNOSIS, DBHelper.KEY_LASTFFUP};
// 
int[] to = new int[] {R.id.fullname, R.id.diagnosis, R.id.lastffup};

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

answered Aug 6, 2013 at 17:05

Dunkey

1,93211 gold badges43 silver badges72 bronze badges

Collectives™ on Stack Overflow

Android Sqlite concat two columns

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related