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 FILENUM
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,
Tracyassuming the name of your table is table1, you can execute the following query:
select * from table1 order by ascii(filenum)
"Tracy R via SQLMonster.com" wrote:
> 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 FILENUM
> 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
>