MSAIT
asked on
Sorting alphanumerical values Datagrid or SQL
Hi,
I have a varchar field which contains page numbers in my sql table.
I am using a Datagrid Control to display the results.
Currently sort it as
1
2
20
21
3
30
F1
F2
F3
but I want to sort it as
1
2
3
4
5
6
20
21
30
F1
F2
F3
Is there a way I can do this ? thanks
I have a varchar field which contains page numbers in my sql table.
I am using a Datagrid Control to display the results.
Currently sort it as
1
2
20
21
3
30
F1
F2
F3
but I want to sort it as
1
2
3
4
5
6
20
21
30
F1
F2
F3
Is there a way I can do this ? thanks
imitchie
what is the SQL statement you are using? have you specified ORDER BY?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
or try:
select * from test
order by
(case when isnumeric(col1)=1 then cast(col1 as int) else ascii(col1) end)
select * from test
order by
(case when isnumeric(col1)=1 then cast(col1 as int) else ascii(col1) end)
create table test
(col1 varchar(50))
insert into test
select '1'
union select '2'
union select '3'
union select '4'
union select '5'
union select '6'
union select '20'
union select '21'
union select '30'
union select 'F1'
union select 'F2'
union select 'F3'
select * from test
order by
(case when isnumeric(col1)=1 then cast(col1 as int) else ascii(col1) end)
another option, by knowing the size of the varchar field
select * from mytable
order by replicate(' ', 10-len(mycol)) + mycol
-- replace 10 with the field size, i.e. 10 for varchar(10)
This should also do it
select * from MyTable
order by Right('00000'+MyCol,5)
That will cause data to be sorted as if it looked like this
00001
00002
00020
00021
00003
00030
000F1
000F2
000F3
select * from MyTable
order by Right('00000'+MyCol,5)
That will cause data to be sorted as if it looked like this
00001
00002
00020
00021
00003
00030
000F1
000F2
000F3
eugene: ascii(col1) sorts only by the first char... so there is no guarantee that F2 will come before F3.
kselvia: 000F2 comes before 00322....
but you would be right if all the non-numeric data has a length <= maximum digits in numeric data
but you would be right if all the non-numeric data has a length <= maximum digits in numeric data
imittchie sniped me. :) His is better.
ASKER
Hi ITCHME ;-) I found this on a previous post but it looks similar to you sol....
ORDER BY CASE WHEN ISNUMERIC(page) <> 1 THEN page ELSE '0' END, CASE WHEN ISNUMERIC(page) = 1 THEN CONVERT(INT,
page) ELSE - 1 END
what do you think? Legit?
ORDER BY CASE WHEN ISNUMERIC(page) <> 1 THEN page ELSE '0' END, CASE WHEN ISNUMERIC(page) = 1 THEN CONVERT(INT,
page) ELSE - 1 END
what do you think? Legit?
that works too
ok then:
select col1,cast(col1 as varbinary ) from test
order by
(case when isnumeric(col1)=1 then cast(col1 as int) else cast(col1 as varbinary ) end)
select col1,cast(col1 as varbinary ) from test
order by
(case when isnumeric(col1)=1 then cast(col1 as int) else cast(col1 as varbinary ) end)
NM:
it is just for your sample data
use standard way that imitchie has posted
it is just for your sample data
use standard way that imitchie has posted