Solved

sort text field like numeric

Posted on 2003-12-05
7
565 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
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 8

Expert Comment

by:dishanf
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:dishanf
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 50 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

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 …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

911 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

22 Experts available now in Live!

Get 1:1 Help Now