Sort numbers (and numbers+letters) with sqlite

Question

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

Answer 1

Easy

select col from tbl order by col*1, col

Answer 2

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.