sql query sorting

Is your field a character (char, varchar, nvarchar) or numeric (int, money, numeric)?
What you describe is typical of character sorting, so to sort numerically you can CAST(that number as numeric(19,4))

This also assumes that every value is a number, otherwise you'd have to weed them out.

it has got character also inside
example c01, c02, c03, c20, C25

It sounds like your column is a varchar column, not an integer or decimal column.  If you can change the column type, you should be okay.  Otherwise you may have to CAST the column value before you sort on  it.

(Nevermind - I see [Jim Horn] beat me)

You can also use Format() in your order By clause.

Say, the maximum charters is 5,

Order By FORMAT(FieldName, ‘00000’)

>it has got character also inside
Just a thought:  That's relevant to your question, so details like this should be included in the question.

Is it always the left-most single character?  If yes, you can remove it..

SELECT value
FROM your_table
ORDER BY CAST(RIGHT(value, LEN(value) - 1)  as int)

Select allOpen in new window

Although you may have to deal with other issues such as blanks or NULL values, more than one letter, or other letters, based on if the data is as advertised here.

i have values like this C0010001F

>i have values like this C0010001F
Before we go further, instead of a 'multiple iterations of ten words or less' approach, how about providing us a data mockup of the data you're working with.

Obviously all those values can't mean the same thing.  Should they all be in the same column?