Query to sort a field such that strings comes first and then numbers

Question

I've a column in which contains numbers or strings. The type of the column is varchar.

Usually when we sort it using the string field, then all the numbers come first and then strings start. But I want all the strings first and then numbers.

TIA !

Answer 1

This worked for me...

Select * from Table order by stringfield+0;

edit: http://www.sqlite.org/datatypes.html (Point 4.0)

UPDATE: Try this....

select * from Table where LENGTH(trim(stringfield,"0123456789 ") )=0 union select * from table order by stringfield;

Answer 2

You'll have to write it in two separate queries. One for selecting numbers, the other for strings. Preferably I would create a second column (one for numbers, one for strings), making it easier and faster to have those two queries run.

Answer 3

How about the following (two queries as suggested above):

select * from Table where LENGTH(trim(stringfield,"0123456789 ")) > 0; select * from table where LENGTH(trim(stringfield,"0123456789 ")) = 0;

The first select should return only values that are not numeric, whilst the second should return only values that are numeric.

For a table that contains a mixture of numeric and string data, this outputs the strings first, then the numbers.

Answer 4

Have you considered creating a custom collation-function? I have never used this myself, but it sounds like exactly what you need.