• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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