Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Help With SQL; order string data like numeric

I have a table of ages, infant, 1, 2... to 21.  The data is text, because an age can be "infant".  However, I would like SQL to sort the records with any instance of "infant" being first, and then as if the data was numeric.

Can this be done?
0
HLRosenberger
Asked:
HLRosenberger
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Order by Case myage
When "infant" then -1
Else myage END;
0
 
RayData AnalystCommented:
The only problem with Phillips suggestion is that if it continues to treat age as a string instead of a number it will not order correctly.  
you'd order like  
1,11,12,13...,2, 20, 21, etc.....


If you cast your values as an int along with Phillip's idea, then you get a 100% certain method.

--Something like this (did not check my syntax)
Order by    cast ( (Case when age = 'Infant' then '0' else age end)  as int)
--IFyou allow for a zero in age already, then you'd want infant to = -1 )
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Phillip's answer is correct, but just in case it isn't obvious, if the age is being stored as a varchar then you're never going to be able to perform math calculations without eliminating 'infant' as a value (which will likely have meaning), and then always converting to a numeric value.

Recommend the column be an integer data type, store infant as 0, and you can always display 'infant' in any front-end applications if you wish.
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.

 
HLRosenbergerAuthor Commented:
Thanks!
0
 
RayData AnalystCommented:
Jim,
The age is stored as text.  Knowing that, can you enlighten me (no sarcasm here), so I can have the knowledge on why/how Phillips answer will sort text strings like numbers?
IE, what condition exists here that makes this conversion 'natural'?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Because it places the string 'infant' as a negative number, which will appears first in a numeric sort order with all other values.  (assuming it's the only other non-numeric value)

>IE, what condition exists here that makes this conversion 'natural'?
Probably implicit conversion with the first value in the CASE statement being numeric.

Although we may have to specifically cast it as a number, such as how Ray has it.  

Either way, storing age as a varchar has multiple problems.

@HLR - You'll have to test to be sure.

Question for you:  Would there ever be a valid scenario where age 3 - age 'infant' needs to be calculated, and if so how can that be performed if the data is stored as a varchar?
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now