[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

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
0
MSAIT
Asked:
MSAIT
  • 6
  • 3
  • 2
  • +1
1 Solution
 
imitchieCommented:
what is the SQL statement you are using? have you specified ORDER BY?
0
 
imitchieCommented:
try this
assuming
select * from mytable
order by
case when isnumeric(mycol) = 1 then 0 else 1 end,
case when isnumeric(mycol) = 1 then cast(mycol as int) else 0 end,
mycol

Open in new window

0
 
Eugene ZCommented:
or try:
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)

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
imitchieCommented:
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)

Open in new window

0
 
Ken SelviaRetiredCommented:
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
0
 
imitchieCommented:
eugene: ascii(col1) sorts only by the first char... so there is no guarantee that F2 will come before F3.
0
 
imitchieCommented:
kselvia: 000F2 comes before 00322....
but you would be right if all the non-numeric data has a length <= maximum digits in numeric data
0
 
Ken SelviaRetiredCommented:
imittchie sniped me.  :)  His is better.
0
 
MSAITAuthor Commented:
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?
0
 
imitchieCommented:
that works too
0
 
Eugene ZCommented:
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)
0
 
Eugene ZCommented:
NM:
it is just for your sample data
use standard way that imitchie has posted
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now