Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help With SQL; order string data like numeric

Posted on 2015-01-22
6
Medium Priority
?
122 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

660 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