1

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
  • 2
    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
  • 1
    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
2

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);
2

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);

enter image description here

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.

2

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
0

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
0

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...

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.