# Sort characters ascending then numbers ascending

Is there an easy trick to change the sort order to "first character values ascending, then numeric values ascending" when selecting?

``````   SELECT mycol
FROM mytable
ORDER BY mycol
``````

Result:

``````1C
8Q
9G
AR
BZ
IT
``````

With DESC:

``````   SELECT mycol
FROM mytable
ORDER BY mycol DESC
``````

Result:

``````IT
BZ
AR
9G
8Q
1C
``````

Desired result:

``````AR
BZ
IT
1C
8Q
9G
``````
• What happens when you get to `'ZZ'` (actually, think it would be `'99'`)? – Larnu Apr 11 at 14:12
• Will your column only have 2 characters? – Luis Cazares Apr 11 at 14:14
• After ZZ all rows starting with numeric values should be listed, like: 1A, 1B,... and so on – Esteban P. Apr 11 at 14:15
• @LuisCazares in the current challenge yes. i'm not sure if i will need this sort order in future for further columns with more characters. – Esteban P. Apr 11 at 14:16
• This kind of logic can be quite confusing, so it is difficult to answer. Which one comes first: `11` or `2A`? (Because 11 is numerically larger than 2...) – Bart Hofland Apr 11 at 14:23

This option will divide the column into 2 to separate digits and letters.

``````SELECT *
FROM (VALUES('1C'),('8Q'),('9G'),('AR'),('BZ'),('IT'))x(MyCol)
ORDER BY SUBSTRING(MyCol, 0, PATINDEX( '%[A-Z]%', MyCol)),
SUBSTRING(MyCol, PATINDEX( '%[A-Z]%', MyCol), 10);
``````

We can try using the `ASCII` function here along with a `CASE` expression:

``````SELECT mycol
FROM mytable
ORDER BY
CASE WHEN LEFT(mycol, 1) LIKE '[A-Z]' THEN 0 ELSE 1 END,
LEFT(mycol, 1),
CASE WHEN RIGHT(mycol, 1) LIKE '[A-Z]' THEN 0 ELSE 1 END,
RIGHT(mycol, 1);
``````

## Demo

The logic here uses four levels of sorting, a pair for each of the two characters in `mycol`. The `CASE` expressions put letters before numbers. After this, we simply sort ascending by number or letter.

One other approach to this would be to treat your column as a base 36 number (10 digits plus 26 letters), and then just sort based on that number converted back to a base 10 decimal. But, this might be uglier and more convoluted than the solution I posted above.

Just simply:

``````SELECT *
FROM (VALUES
('1C'),
('8Q'),
('9G'),
('AR'),
('BZ'),
('IT'),
('11'),
('2A')
) AS tbl(col)
ORDER BY CASE WHEN col LIKE '[0-9]%' THEN 2 ELSE 1 END
, col
``````

Output:

``````AR
BZ
IT
11
1C
2A
8Q
9G
``````

Try this:

``````DECLARE @mytable TABLE
( MyCol VARCHAR(3))

insert @mytable VALUES ('IT'),('BZ'),('AR'),('1C'),('8Q')

SELECT
MyCol
FROM @mytable
ORDER BY PATINDEX('[0-9]%',MyCol),MyCol
``````
• This will only sort on the first numeric digit, so it will put '10A' directly before '1C' – High Plains Grifter Apr 11 at 14:24

My variant on @TimBiegeleisen's and @SalmanA's answers:

``````SELECT mycol
FROM mytable
ORDER BY
CASE WHEN mycol LIKE '[^0-9]%' THEN 0 ELSE 1 END,
mycol;
``````

(I don't know if indexes on columns in the ORDER BY clause can affect performance, but to be sure I would avoid function calls.)

Edit: Well, actually it's practically the same answer as @SalmanA's one...

Another solution i figured out would be to use `COLLATE` for the `ORDER BY` clause, using an EBCDIC collation. Performancewise as fast as other solutions. Maybe easier to write, but somehow tricky to read without comments. An advantage would be, it is working for columns with more than 1 or 2 characters too.

``````SELECT mycol
FROM mytable
ORDER BY mycol COLLATE SQL_EBCDIC278_CP1_CS_AS
``````