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

Question

By default, SQL orders numbers before characters.

So, if I have a column "name":

abc ab1 a1b 1ba 1bac b21 

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

 SELECT * FROM ... ORDER BY name 

will result in:

 1ba 1bac a1b ab1 abc abc1 b21 

But I want it to be sorted by a-0 (first aZ, then 0-9).

 abc abc1 ab1 a1b b21 1ba 1bac 

How to do this in a request? In particular, how to do this in SQLite?

I found one solution in Sort MySQL results in alphabetical order, but with the numbers of the latter , but only for the first char.

Answer 1

I would suggest SELECT in another column, say name_replace , with numbers replaced by a high ASCII character (e.g. ~ ), then sorted by that column and then by name. Unfortunately, SQLite does not support regular expression replace:

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

The numbers will be the last when sorted by name_replace . Sorting by name will be sorted by numbers.

Answer 2

This is a trick with the data provided.

 SELECT * FROM Table ORDER BY Name COLLATE SQL_EBCDIC037_CP1_CS_AS 

However, you can look at various types of sorting to ensure that it does what you want in all directions.

UPDATE: You mentioned SQLite, but I tested it in MSSQL. Not sure if this sorting is available in SQLite, but the comments below may have useful information.

Answer 3

Test this data please

 declare @t table(a char(3)) insert @t values('ab1') insert @t values('a1b') insert @t values('1ba') insert @t values('b21') insert @t values('12a') insert @t values('13b') select a, patindex('[0-9]%', a + 'a'), patindex('_[0-9]%', a + 'a'), patindex('__[0-9]%', a + 'a') from @t order by 2, 3, 4, 1 

or

 select a from (select a, patindex('[0-9]%', a + 'a') b, patindex('_[0-9]%', a + 'a') c, patindex('__[0-9]%', a + 'a') d from @t) e order by b, c, d, a 

Answer 4

This is my idea: you add another "help_column", which checks if the first char is a number and assigns it 1, otherwise assign 0 and then sort by this column and then by name:

 select *, case when substring(name,1,1) like '[0-9]' then 1 else 0 end as help_order from ( select 'ab1' as name union select 'a1b' as name union select '1ba' as name union select 'b21' as name ) a order by help_order, name 

Of course, you may need to improve the regex [0-9] to handle more than one number if necessary.

And, of course, you must replace the internal query (one with multiple joins with your table).

Answer 5

Thsi replaces 0-9 with a higher ASCII 122 (this is lowercase z).

SQLLite does not have a CHAR function for substituting character code (e.g. CHAR(123) to CHAR(132) ), which may be needed instead of my attempt to CHAR (123)

Of course, untested :-)

 ORDER BY REPLACE REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(name, '0', '{0') , '1', '{2') , '2', '{2') , '3', '{3') , '4', '{4') , '5', '{5') , '6', '{6') , '7', '{7') , '8', '{8') , '9', '{9') 

Edit: although the @David Faber solution does the same thing, but a little easier ...