CODEWITHSUNDEEP
androidsqlitesorting
SKBasha
SKBasha

Reputation: 73

Natural sorting of alphanumeric values in sqlite using android

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?

Upvotes: 7

Views: 2313

Answers (3)

praveenb
praveenb

Reputation: 10669

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.

Upvotes: 7

Arun Shankar
Arun Shankar

Reputation: 2593

Updated

You can use different ways - Some of are mentioned below:

BIN Way

SELECT 
tbl_column, 
BIN(tbl_column) AS binray_not_needed_column
FROM db_table
ORDER BY binray_not_needed_column ASC , tbl_column ASC

Cast Way

SELECT 
tbl_column, 
CAST(tbl_column as SIGNED) AS casted_column
FROM db_table
ORDER BY casted_column ASC , tbl_column ASC

or try the solution:

There are a whole lot of solutions out there if you hit up Google, and you can, of course, just use the natsort() function in PHP, but it's simple enough to accomplish natural sorting in MySQL: sort by length first, then the column value.

Query: SELECT alphanumeric, integer FROM sorting_test ORDER BY LENGTH(alphanumeric), alphanumeric from here

Upvotes: -1

Drako
Drako

Reputation: 768

I can propose using regex replacement adding zeros, creating temporary table of original and corresponding values, then follow this link for sorting it: http://www.saltycrane.com/blog/2007/12/how-to-sort-table-by-columns-in-python/ tip for regex add as many zeros after last letter, but limit the number of total digits for predicted maximum number of digits. If You need help with regex as well, provide exact info of valid and invalid values, so can halp with that too. PS if want to be sure that zeros goes before last digits search for char from the end

Upvotes: 0

Related Questions