T-SQL Not staying in order

Question

I have created a database in SQL Server console C# where there are different groups added to a row vertically, eg G1, G2, G3 etc.

This is ok for what is needed, however when a value is added like so G10, it automatically put itself under G1, so instead of

G1
G2
G3
G10

it looks like this:

G1
G10
G2
G3

I have tried the following query

SELECT * 
FROM mondayTable 
ORDER BY SAAT ASC

and

ORDER BY SAAT DESC

but it still stays in the same order, but from top to bottom, bottom to top.

Here is a screenshot of what it looks like with ORDER BY DESC:

is the format of `SAAT` column fixed?i.e an alphabet followed by a number? — Vamsi Prabhala, Jul 21 '16 at 20:04
This is because the SAAT column is alphabetic, so the sort is alphabetic not numerical. — Kevin, Jul 21 '16 at 20:05
Since there are `G` characters in the column, it must be of datatype `varchar` presumably - and as a **string**, it's ordered **correctly**. — marc_s, Jul 21 '16 at 20:05
hey guys, sorry I should have mentioned that `SAAT` set to `varchar(50)` — user1234433222, Jul 21 '16 at 20:07
Literally first answer when i googled how to sort alphanumeric values in sql server http://stackoverflow.com/questions/20240313/sql-server-2008-order-by-strings-with-number-numerically — AVK, Jul 21 '16 at 20:07

score 3 · Accepted Answer · answered Jul 21 '16 at 20:08

3

As commenters pointed out it's because your data is alphabetic so the sort is correct. You could use something like this:

SELECT * 
FROM mondayTable 
ORDER BY CAST(SUBSTRING(SAAT,2,LEN(SAAT)) AS INT)

answered Jul 21 '16 at 20:08

squillman

Wow! how about that! Thanks Squillman for helping out :) I don't to annoy you however, could you explain for me how your answer works? I don't want to be a control c control v junkie :) – user1234433222 Jul 21 '16 at 20:13
@Werdna The ORDER BY strips off the numeric characters and casts them as an integer value. Since the integer value is numeric it provides the sort you are looking for. This does assume a single character in the string value at the beginning and that the numeric characters start at position 2. – squillman Jul 21 '16 at 20:17
Thank you Squillman for explaining that I understand now, Thank you again, I hope you have a good weekend :) – user1234433222 Jul 21 '16 at 20:26

Jersey · Answer 2 · 2016-07-21T20:47:51.450

This is normal behavior because you are sorting on characters. To place it in order I would pad the numbers after the "G".

For example:

G01
G02
G03
G04
G05
G06
G07
G08
G09
G10
G11

OR if you think the numbers will go into the 1000's

G0001
G0002
G0003
G0004
G0005
G0006
G0007
G0008
G0009
G0010
G0011

an easy way to pad is

SELECT 'G'+ RIGHT('000'+CAST(2 as varchar(4)),4)

SELECT 'G'+ RIGHT('000'+CAST(11 as varchar(4)),4)

This will generate

G0002
G0011

Another way could be to do order by like this

ORDER BY CAST(REPLACE(SAAT,'G','') AS INT) ASC

2 Answers2