sort text field like numeric

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
LVL 2
chrishqAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
LowfatspreadConnect With a Mentor Commented:
Hi chrishq,
select * from table
order by case isnumeric(column) = 1 then Right("0000000000" + ltrim(rtrim(column)),10) else column end

Cheers!
0
 
Dishan FernandoSoftware Engineer / DBACommented:
try this...

SELECT ColName
FROM TableName
ORDER BY ColName
0
 
chrishqAuthor Commented:
no that wont work thats what im trying now and will result in 239 coming before 26 etc
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
Dishan FernandoSoftware Engineer / DBACommented:
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
 
namasi_navaretnamCommented:
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
 
LowfatspreadCommented:
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
 
chrishqAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.