How can I sort strings numerically

[jhowe]

I have a varchar field that I want to sort numerically. Typical values are:

1A
1B
5
5A
9
10
10A
12B

and I essentially want to parse the integer at the beginning and sort on that integer. Is this possible in an ORDER BY query? Currently sorting this field gives the alphanumeric order

1A
1B
10
10A
10B
5
5A
9

Thanks in advance.

jhowe

 

[foxdev]

Without putting in a leading space or '0' character, I can't see how you could do this.
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[jballum]

This will only work if the last character is the only one that might be a letter.

SELECT
F1,
'JustNum' =
CASE
WHEN ISNUMERIC(F1) = 1 THEN CONVERT(INT, F1)
ELSE CONVERT(INT, SUBSTRING(F1, 1, LEN(F1)-1))
END
from
tablename
ORDER BY
'JustNum', F1

JB