Learn how to a build a cloud-first strategyRegister Now

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

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
0
chrishq
Asked:
chrishq
  • 2
  • 2
  • 2
  • +1
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LowfatspreadCommented:
Hi chrishq,
select * from table
order by case isnumeric(column) = 1 then Right("0000000000" + ltrim(rtrim(column)),10) else column end

Cheers!
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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