CODEWITHSUNDEEP
sqlsqlitesortingsql-order-by
Susan
Susan

Reputation: 277

Sort numbers (and numbers+letters) with sqlite

I'm trying to sort a VarChar field with Sqlite.

The field can contain numbers or numbers+letters, but I need to sort in numerical order, like this:

1
1a
1b
5
5x
5y
10
10d
10e
10g1
11
11a
11b
100c
100f

Any ideas? I've been able to do this... it is close (but not quite) what I need:

Pad the start of the field with '00000', and then sort on the 1st five letters

Upvotes: 5

Views: 5805

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

Easy

select col from tbl order by col*1, col

Upvotes: 13

dan04
dan04

Reputation: 91189

There's no easy way to do this with the built-in functions. Use sqlite3_create_collation (or the equivalent wrapper in your preferred programming language) to define a string comparison function that implements natural sort.

Upvotes: 1

Related Questions