Asp.net gridview Sorting by age

Question

I have a table with column "Age", where sorted age is shown like:

10 years,
105 years
18 years,
60 years.
8 months.

How can I sort it not in alphabetical order, but in correct. I can't eliminate word "years".

Answer 1

Well, if this is from say a database?

Then you could do this:

SELECT id, FirstName, LastName, Age, 
  cast(SUBSTRING(Age,1,Charindex(' ',Age)-1 ) as integer) as NumAge
  FROM tblCustomers
  ORDER BY NumAge

So, now you have a text display column, but you also have a real "number" column with the actual number in years - and you can sort correctly on that NumAge column. And this sort could also be done with a data-view sort - so however you were/are sorting the data, then sorting by NumAge will become a possible choice if you add the above column that plucks out the number from that column.

Now the Above works if we don't have a mix of month, and years. But, to handle that, then we change above to this:

SELECT id, FirstName, LastName, Age, 
    CASE
      WHEN CharIndex('month',Age) > 0 THEN
        (cast(SUBSTRING(Age,1,Charindex(' ',Age)-1 ) as integer))
      WHEN CharIndex('year',Age) > 0 THEN
        (cast(SUBSTRING(Age,1,Charindex(' ',Age)-1 ) * 12 as integer))
    END 
     AS AgeInMonths
  FROM tblCustomers
  ORDER BY AgeInMonths

So, once again, we now have a number and value in months for age, and this can be sorted. So we display the Age + text column, but the sort expression can use AgeInMonths and the result is data in the correct order.

And if you have days? Then I would in place of the months * 12, using months * 30, and for year, then just use * 365. So, it not at all hard to have support for years, months, or days.

The result is once again, data sorted in number order by given age.

Answer 2

The problem is also "months". Is 8 months higher than 2 years or is 8 months 0 year? If it is the last you can use Linq. Lets say this are the ages.

var age = new List<string>()
{
    "10 years,",
    "105 years",
    "18 years,",
    "60 years.",
    "8 months.",
    "1 year.",
    "18 months,",
 };

Then you can do this. It splits the list in months and years. Then removes all characters except numbers. If it is a month divide it by 12 to get the years. Then sort it, and make it a string again and add "years" to it.

var sortedAge = age.Where(x => x.ToLower().Contains("month"))
   .Select(x => Convert.ToInt32(string.Concat(x.Where(Char.IsDigit))) / 12)
   .Concat(age.Where(x => x.ToLower().Contains("year"))
   .Select(x => Convert.ToInt32(string.Concat(x.Where(Char.IsDigit)))))
   .OrderBy(y => y)
   .Select(z => z > 1 ? z.ToString() + " years" : z.ToString() + " year").ToList();

result

0 year
1 year
1 year
10 years
18 years
60 years
105 years

But you would still be better off just storing the age as an int in the DOB or even better, the date of birth as datetime.