ORDER BY varchar with [a-9] instead of [0-Z] in SQL

Question

By default, SQL orders numbers before characters.

So if I have the column "name":

abc
ab1
a1b
1ba
1bac
b21

Since SQL sorts by 0-Z (first 0 to 9, then a-Z), the query

SELECT * FROM ... ORDER BY name

will result in:

1ba
1bac
a1b
ab1
abc
abc1
b21

But I want it to sort by a-0 (first a-Z, then 0-9).

abc
abc1
ab1
a1b
b21
1ba
1bac

How do I do this in a query? More specifically, how do I do this in SQLite?

I found one solution in Sort MySQL results alphabetically, but with numbers last, but only for first char.

Answer 1

I would suggest SELECTing another column, say name_replace, with the digits replaced by a high-ASCII character (such as ~), then sorting on that column then on name. Unfortunately SQLite doesn't have support for regular expression replace:

SELECT name, replace( ... replace(replace(name, '0', '~'), '1', '~') ... '9', '~') AS name_replace
  FROM mytable
 ORDER BY name_replace, name

The digits will come last when sorting on name_replace. Sorting on name will then order by the digits.

Answer 2

This does the trick with the data provided.

SELECT *
FROM Table
ORDER BY Name COLLATE SQL_EBCDIC037_CP1_CS_AS

However you may want to look into the various collation types to ensure it does what you want across the board.

UPDATE: You mentioned SQLite however I tested this on MSSQL. Not sure if this collation is available in SQLite but comments below may have useful info.