?
Solved

Help With SQL; order string data like numeric

Posted on 2015-01-22
6
Medium Priority
?
134 Views
Last Modified: 2015-01-22
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
Comment
Question by:HLRosenberger
6 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40565036
Order by Case myage
When "infant" then -1
Else myage END;
0
 
LVL 11

Expert Comment

by:Ray
ID: 40565061
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40565090
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 40565120
Thanks!
0
 
LVL 11

Expert Comment

by:Ray
ID: 40565137
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40565152
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

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.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

569 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