asked on

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?

ASKER CERTIFIED SOLUTION

Phillip Burton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray

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 )

Jim Horn

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.

HLRosenberger

ASKER

Thanks!

Ray

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'?

Jim Horn

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?