Solved

Sorting alphanumerical values Datagrid or SQL

Posted on 2007-11-18
12
583 Views
Last Modified: 2012-06-21
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
Comment
Question by:MSAIT
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
what is the SQL statement you are using? have you specified ORDER BY?
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
Comment Utility
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
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
eugene: ascii(col1) sorts only by the first char... so there is no guarantee that F2 will come before F3.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 
LVL 12

Expert Comment

by:kselvia
Comment Utility
imittchie sniped me.  :)  His is better.
0
 
LVL 2

Author Comment

by:MSAIT
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
that works too
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
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
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
NM:
it is just for your sample data
use standard way that imitchie has posted
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now