SQL ORDER BY Numeric & Alpha

[fowlerjp]

I have records that represent processes, which contain a sequence number. Now with out re-sequencing the whole process they need to insert additional steps. For example new data records would look like this” 1,2,2a,3,4,4a,4b…… etc. I changed the field type from numeric to text to allow numeric and alpha charters. This screws up my SELECT ORDER statement. Now it won’t even sort the numbers right. Is there a way to do this? Here is what I have now.

SELECT * FROM JobProc ORDER BY Sequence ASC

How can a sort the data in order?

[JHausmann]

Change the field back to numeric, add a nullable char field called Alpha (size=1 unless you plan on getting into double letters).

then

SELECT * FROM JobProc ORDER BY Sequence, alpha ASC

[RIVES]

what exactly is your datatype definition?

char is different from varchar.

e.g.
assuming char(4)

4 would be...char = ' 4'

the sum of the asciis would be different.

you have to pad it with zero's to make it '0004' before padding it with 'a' , 'b' , 'c'. Don't forget to pad it with ' ' if no a or b or c etc.

'004 '

Hope this helps.

[RIVES]

to be more descriptive, lets assume that a space is indicated by an *.

char(4)... your '4' would be '***4'

manipulate it so that it would be '004*'