How to use order by with modified column-CodePudding

There is a Table with a age-column. The Value of this column is a varchar because the age of persons under the age of 1 is saved in months with an additional 'm' (9 Month old -> '9m')

I know that this is generally a bad idea and one should rather persist the date of birth, but in this case the age refers to the age on a specific day in history - and additionally this is part of a lesson and the whole point is learning how to treat "weird" data.

My first idea was to put a leading zero on all ages which are not purely numeric:

SELECT * 
FROM db 
ORDER BY REPLACE(age, (IF ISNUMERIC(age) age ELSE CONCAT('0', age))) DESC;

However this is not a valid SQL-statement and neither are my other attempts. The question is: How can I adjust the value used for ORDER BY without altering the db?

Another approach would be to select only the rows with a purely numeric age value and a separate select for the remaining rows order both of them separately and combine them afterwards.

My take on this was the following:

(SELECT name, age 
 FROM titanic 
 WHERE ISNUMERIC(age) 
 ORDER BY age DESC) 
UNION 
(SELECT name, age 
 FROM titanic 
 WHERE NOT ISNUMERIC(age) 
 ORDER BY age);

This is in fact valid or at least it gives me a result. But in the result I can't really see what happened to the order, it looks like the UNION undos everything.

Thanks in advance, will take any tip or even just the name of the function/method I should look into!

CodePudding user response：

Would this work?

SELECT name, age FROM titanic ORDER BY isnumeric(age), age

CodePudding user response：

I would use "Case When" structure with some transformations in the "Order by" to get the total number of months for both types of ages.

Select name, age
From tbl
Where age SIMILAR TO '[1-9][0-9]*' Or
      age SIMILAR TO '[1-9][0-2]?m'
Order by Case When age SIMILAR TO '[1-9][0-2]?m' Then Substring(age,1,CHAR_LENGTH(age)-1)::int
              When age SIMILAR TO '[1-9][0-9]*' Then age::int * 12 End