alphanumeric sort on inconsistent values

I am running SQL Server 2000 and must sort on an alphanumeric field.  There
is no pattern consistency for the values in the column (let's call it FILENU
M
varchar(30)).  I am having a difficult time coming up with a solution.

Given a set of filenumbers:

1dd
1
1x4
1cc2
1110-345-720a3
11
380-41-3a
10

Should be sorted as:

1
1cc2
1dd
1x4
10
11
380-41-3a
1110-345-720a3

any assistance would be greatly appreciated.


Thanks,
Tracy

assuming the name of your table is table1, you can execute the following que
ry:

select * from table1 order by ascii(filenum)

"Tracy R via webservertalk.com" wrote:

> I am running SQL Server 2000 and must sort on an alphanumeric field.  Ther
e
> is no pattern consistency for the values in the column (let's call it FILE
NUM
> varchar(30)).  I am having a difficult time coming up with a solution.
>
> Given a set of filenumbers:
>
> 1dd
> 1
> 1x4
> 1cc2
> 1110-345-720a3
> 11
> 380-41-3a
> 10
>
> Should be sorted as:
>
> 1
> 1cc2
> 1dd
> 1x4
> 10
> 11
> 380-41-3a
> 1110-345-720a3
>
> any assistance would be greatly appreciated.
>
>
> Thanks,
> Tracy
>