1

I use standard approach to display my database records in the ListView:

recordsCursor = mDb.query(DATABASE_BL_TABLE, new String[] {KEY_BL_ROWID, KEY_BL_SENDER, KEY_BL_ADDED}, null, null, null, null, KEY_BL_SENDER);
startManagingCursor(recordsCursor);

String[] from = new String[]{DbAdapter.KEY_BL_SENDER};
int[] to = new int[]{R.id.text1};
adapter = new SimpleCursorAdapter(this, R.layout.mylist_row, recordsCursor, from, to);
setListAdapter(adapter);

So, all records are sorted by KEY_BL_SENDER. I would like to implement the following sorting logic:

  1. ignore case (so, the list of 4 elements - aa, bb, AA, BB will be displayed in the following order: AA, aa, BB, bb);
  2. ignore other symbols besides letters (the list of 4 elements - ** aa **, ~bb~, aa, __BB__ will be displayed in the following order: aa, ** aa **, __BB__, ~bb~).

How could I do it?

LA_
  • 19,823
  • 58
  • 172
  • 308
  • Take a look at: http://stackoverflow.com/questions/9898227/rearrange-list-view-items-in-simplecursoradapter – Italo Borssatto Nov 08 '13 at 17:03
  • Thanks, @italo. I found how to ignore case - http://stackoverflow.com/a/7934364/604388. But your link doesn't help me with task 2. – LA_ Nov 08 '13 at 17:07
  • "*ignore case ... capital letters ideally should be first*" - You might want to clarify that. It's contradictory right now. – Geobits Nov 08 '13 at 17:12
  • Just to clarify - it shows how to implement own sorting logic, but doesn't show how to apply that to my query. – LA_ Nov 08 '13 at 17:13
  • Your (1) is contradictory as you require a specific way to sort. No 2 I dont think is possible by using a SQL query, you will probably have to do that in code. – sprocket12 Nov 08 '13 at 17:27
  • @Geobits, thanks for your comment - I've removed this statement. Actually, I wanted to say that if there are two equal values and just case is the difference, then capital letters should go first (i.e. `A`, `a` and not `a`, `A`). – LA_ Nov 08 '13 at 19:01
  • Why did you edit that part out then? "Ignore case" means that capital letters *don't* necessarily go first. You should have removed the "ignore case" part if that's what you want. – Geobits Nov 08 '13 at 19:08
  • @Geobits, I've removed this part since it is not so important. The most important part is to ignore case. – LA_ Nov 09 '13 at 09:02

1 Answers1

2

Let's join some known solutions:

Your query would look like:

SELECT ... 
FROM ... 
WHERE ... 
ORDER BY 
    TRIM(KEY_BL_SENDER, "_*~ #$") 
    COLLATE NOCASE ASC;
Community
  • 1
  • 1
Italo Borssatto
  • 15,044
  • 7
  • 62
  • 88
  • Thanks, italo. It looks really simple. Is there any approach to replace ALL characters except of letters instead of having many-many `REPLACE`s? – LA_ Nov 08 '13 at 20:26
  • Hmm. Do I understand `TRIM` correctly - it will remove characters in the beginning and the end, but will not help with cases like `*a*b*c*`, `abc`, where `REPLACE` helps? PS. Don't worry - I'll accept your answer, just would like to clarify it further ;). – LA_ Nov 09 '13 at 09:06
  • Yes. You're right. Trim will take care of your 2nd requirement, but will not solve cases like a*b*c. In this last case you will need a cascate of replaces. Take a look at http://www.sqlite.org/lang_corefunc.html to find more functions. – Italo Borssatto Nov 09 '13 at 11:50