How to order by letters then by numbers?

Question

How do I order query results by alphabetic characters before ordering by numerical values in SQL-Server?

I have myTable with aColumn that I would like to query

I found this, which says to try something like:

SELECT * FROM dbo.myTable
WHERE aColumn LIKE '%val'
ORDER BY IF(aColumn RLIKE '^[a-z]', 1, 2), aColumn

however this was for MySQL, which could be the reason why this does not work in SQL-Server. Is there anything like this for SQL-Server with the Regular expression filter? The error message I get for this is:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'IF'
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'RLIKE'

What I would like to see is something like:

Aval
Bval
Cval
1val
2val

rather than

1val
2val
Aval
Bval
Cval

Answer 1

You can use LIKE predicate :

SELECT m.* 
FROM dbo.myTable m
WHERE aColumn LIKE '%val'
ORDER BY (CASE WHEN aColumn like '[a-z]%' THEN 0 ELSE 1 END), aColumn;

Answer 2

I'm guessing there will some edge cases where numbers could show up or other ways you'd like to sort. Here is a solution that should be flexible:

DECLARE @TOTALS TABLE
(
  ID INT IDENTITY,
  THEDATA VARCHAR(255)
)

INSERT INTO @TOTALS (THEDATA)
SELECT THEDATA FROM atest 
WHERE THEDATA NOT LIKE '%[0-9]%'
ORDER BY THEDATA ASC

INSERT INTO @TOTALS (THEDATA)
SELECT THEDATA FROM atest 
WHERE THEDATA LIKE '%[0-9]%'
ORDER BY THEDATA ASC

SELECT * FROM @TOTALS
ORDER BY ID ASC

What you can do is make a table variable and use various SELECT statements to populate that table. Since it has an IDENTITY column it can keep track of the order items were inserted. Then just SELECT back out by that order!

In this case I'm simply putting all the values that don't have numbers in (sorted) and then all the ones that do have numbers in (sorted).

I hope this helps :)