Sorting data alpha numerics

[TRACEYMARYLAND]

Hi i have
data in a table that is
being displayed as

37571-1-A009
37571-1-A008
37571-1-A0038
37571-1-A0037
37571-1-A0036
37571-1-A0035
37571-1-A0034
37571-1-A0033
37571-1-A0032
37571-1-A0031
37571-1-A0030
37571-1-A0029
37571-1-A0028
37571-1-A0027
37571-1-A0026
37571-1-A0025
37571-1-A0024
37571-1-A0023
37571-1-A0022
37571-1-A0021
37571-1-A0020
37571-1-A0019
37571-1-A0018
37571-1-A0017
37571-1-A0016
37571-1-A0015
37571-1-A0014
37571-1-A0013
37571-1-A0012
37571-1-A0011
37571-1-A0010


and I need it in order i.e
8, 9,10, 11, 12,
etc

Where do i begin.....is it possible to do

Cheers

 

[WhiteKnight57t]

Select * FRom Blah ORDER BY <Field> DESC

 

[TRACEYMARYLAND]

The Order by will not give it to me ....
The data is what it is doing by using ORDERY BY either ASC, or DESC

 

[donutman]

It would be easy to do it for the rows shown, but can we count on the 37571- not turning into 1234-? What I'm suggesting is the possibility of sorting on the numeric value of the varchar field after the 9th character.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

As Karl says, if you can rely on the sort digits always starting at character ten, then you could do:

ORDER BY Convert(int,substring(YourColumn,10,4))
or
ORDER BY Right('0000'+substring(YourColumn,10,4),4)

But if the digits before the numbers at the end could be longer or shorter, you'd need to do something like

ORDER BY Convert(int,Right(YourColumn,CharIndex('-',Reverse(YourColumn))-2))

Note that that would break if for some reason you had a value with no characters after the dash like

37571-1-

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[TRACEYMARYLAND]

I just been told it will be "three only"
010

then when got to 999 it becomes to 00A

Which is hex..........

Does any one know how to add one to the int as HEX Counting control?

 

[ESquared]

Don't convert to int, just sort as text, A comes after 9 and you're fine.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[donutman]

You may have to add a 0 if the length of the string is 12.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

I meant add to the beginning of the number portion. Better expressed as an insert!
-Karl

 

[ESquared]

If it's three only, then it's as simple as:

ORDER BY Right(YourColumn,3)

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[donutman]

Good point! The joys of working with varchar instead of integer.

 

[ESquared]

No, I was wrong...

00A comes after 009 but before 090 or 900.

I need to know the 'counting' scheme before I can write SQL to sort on it.

What comes after 00A? 00B? does it go to 00Z? What next?

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)