Natural sorting of alphanumeric values in sqlite using android

Question

I have a list of names of starts with characters and end with numbers like: -

ka1, ka10, ka 2, ka, sa2, sa1, sa10, p1a10, 1kb, p1a2, p1a11, p1a. 

I want to sort it in natural order, that is: -

1kb, ka, ka1, ka 2, ka10, p1a, p1a2, p1a10, p1a11, sa1, sa2, sa10. 

The main problem I am seeing here is no delimiter between text and numeric part, there also a chance of without numeric part also.

I am using sqlite in android, I can do sorting using java after fetching points by cacheing cursor data, but I am using(recommended to use) cursor adapter.

Please suggest a query for sorting or is there any way to apply sorting in cursor?

Answer 1

I tried below query for Natural sorting:

SELECT 
    item_no
FROM
    items
ORDER BY 
   LENGTH(item_no), item_no;

It worked for me in Sqlite db too. Please see this link, for more details.