When I do something like:

SELECT Line FROM SomeFile ORDER BY 1

and the Line field is a left-justified char string of 1 or 2 chars, and I really want to sort by PADL(Line,2) for some reason issuing:

SELECT PADL(Line,2) FROM SomeFile ORDER BY 1

does NOT result in a properly sorted query.

Yet, because I know that Line is ALWAYS made of numbers, if I use:

SELECT TRANSFORM(VAL(Line),"99") FROM SomeFile ORDER BY 1

it sorts correctly! I believe this is because it's actually keying off the VAL function, not the resulting TRANSFORM'd char value but still, why should this be so? Is it SQL convention to ignore leading spaces? If so, how can one properly sort in situations like this - given that this is a simple case and one might have Line ranging between, say, 0 and 99 OR A through ZZ and the desire is to have the sort treat Z as less than AA?

In the last case, I have no idea how to approach it. Any help appreciated.

TIA,

-Arne