CODEWITHSUNDEEP
sqliteandroid-sqlitesql-order-bysystem.data.sqlite
Robert Smith
Robert Smith

Reputation: 634

Sqlite custom order by of [character]-[number]

I have the following records in sqlite. This is the id field:

id
=====
B-1
B-2
B-10
B-11

I would like to sort as:

B-1
B-2
B-10
B-11

But when I sort, it is sorting as:

B-1
B-10
B-11
B-2

This is the sql

select * from mytable
order by id

How can I get it to sort as:

B-1
B-2
B-10
B-11

All the data is in the format of:

[character]-[number]

Upvotes: 0

Views: 382

Answers (1)

forpas
forpas

Reputation: 164139

You must sort the table first by the string part of the id before the dash and then by the number after the dash.
So you need string functions to extract the string and the numeric part of the column:

select * from mytable
order by 
  substr(id, 1, instr(id, '-')),
  substr(id, instr(id, '-') + 1) + 0

The arithmetic operator + will enforce an implicit conversion of the numeric part of the string to a number.
See the demo.
If the pattern of the ids is exactly like your sample data and there is always 1 letter at the start, followed by a dash and then a number, then the code can be simplified:

select * from mytable
order by
  substr(id, 1, 1),
  substr(id, 3) + 0

See the demo.

Results:

| id   |
| ---- |
| B-1  |
| B-2  |
| B-10 |
| B-11 |

Upvotes: 1

Related Questions