[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

sort text field like numeric

Posted on 2003-12-05
7
Medium Priority
?
573 Views
Last Modified: 2008-03-03
Hi, i have a text column in sql server (varchar 4) which mainly contains numeric data however there are some exeptions that have to be there.

When i select all from this column i want it sorted like a numeric column so that results are returned like:

26
34
239
270
350
A
H
Z

The problem is they are currently returned like:

239
26
270
34
350
A
H
Z

Any ideas on how to do this in a select statment and removing the letters and changing to numeric is NOT an option.

Thanks
Chris
0
Comment
Question by:chrishq
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9886760
try this...

SELECT ColName
FROM TableName
ORDER BY ColName
0
 
LVL 2

Author Comment

by:chrishq
ID: 9886892
no that wont work thats what im trying now and will result in 239 coming before 26 etc
0
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9886926
ok I think you cant direct way to do this..
try..

integers...

SELECT ColName INTO #Temp
from TableName
where patindex('%[a-z]%',ColName) = 0
order by CAST(ColNameAS int)

varchars...

INSERT INTO #Temp
SELECT ColName
from TableName
where patindex('%[a-z]%',ColName) <> 0

SELECT ColName
FORM #Temp
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.

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 200 total points
ID: 9887431
Hi chrishq,
select * from table
order by case isnumeric(column) = 1 then Right("0000000000" + ltrim(rtrim(column)),10) else column end

Cheers!
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9887512
Here is a single query that will do

select *
from mytable
order by  case when ISNUMERIC(yourcol) = 1 then cast(yourcol as int)  else 9999999999 end asc, yourcol

HTH
Namasi Navaretnam
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9887661
hmmm something i forgot to ask ...
could your column contain signed values...

but either namasi's or my solution should work for you

wonder which is faster

the integer sort with 2 columns or the character (or varchar) (10) single column sort..  
0
 
LVL 2

Author Comment

by:chrishq
ID: 9890390
Thanks everyone Lowfatspreads solution seems to work the fastest and is most straight forward - and no there are no signed values in that column
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

650 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