sql query sort varchar field by digits without the characters

is it possible to drop the alphabet characters from a varchar field.

[topr8]

sqlserver

have a situation where i have a field with 'years' in it, which is actually a varchar field ... because the years are held thus ...

for example

1995
1990s
c1980

i now have a need to sort the column by date, is it possible to drop the trailin 's' and the leading 'c' somehow on the server before doing the sort?

actually the trailing 's' isn't a problem, but the leading 'c' is.

of course i need to keep the field like this for other reasons, i could create another field with just the numeric part of the 'year' but i'd rather not if i can help it.

[duckhunter]

Use the Select Case function to define a new field (Code in this example) and sort by that value. You might need to convert(int,code) so it sorts properly (ie: 101 will come before 1000 when using an INT and 1000 will come before 101 when using a VARCHAR)

SELECT
Code = Case 
WHEN UPPER(LEFT(mycode,1)) = 'C' THEN RIGHT(RTRIM(mycode),LEN(mycode)-1)
WHEN UPPER(RIGHT(mycode,1)) = 'S' THEN LEFT(RTRIM(mycode),LEN(mycode)-1)
ELSE mycode
END
FROM MyProductTable 
ORDER BY CODE