|
Home > Archive > MS SQL Server > August 2005 > alphanumeric sort on inconsistent values
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
alphanumeric sort on inconsistent values
|
|
| Tracy R via SQLMonster.com 2005-08-31, 8:23 pm |
| 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
| |
|
| assuming the name of your table is table1, you can execute the following query:
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. 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
>
|
|
|
|
|