Selecting and sorting numbers from mysql varchar column

Question

I have a db of deceased persons; one of the columns contains their age at death. Since the db records the information in the historical documents "as is", the column contains things other than numbers. An age might be entered as "inf.", "6 mos.", "2 wks." etc, or be empty. About 90% of the column is plain old numbers like "87" or "2".

I'm trying to echo out a table of the oldest persons, sorted by age. Because of all the non-numeric values of age my query is:

<?php
...

$sql = "SELECT pid,first_name,surname,date,age
FROM $table
WHERE age REGEXP '^[0-9]+$'
ORDER BY age DESC
LIMIT 10";

...
?>

After age 90 the results are always kids who died aged 9. The column is being sorted in alphabetical order.

How do I sort in descending numerical order?

Possible duplicate of [SQL order string as number](https://stackoverflow.com/questions/11808573/sql-order-string-as-number) — Micha Wiedenmann, Aug 15 '17 at 10:13
Welcome to StackOverflow. Your question could be improved by asking the generic question with all the irrelevant details removed. — Micha Wiedenmann, Aug 15 '17 at 10:14

Michael · Accepted Answer · 2017-08-15T10:25:21.580

2

SELECT pid,first_name,surname,date,CAST(age as SIGNED) AS int_age
FROM table1
WHERE age REGEXP '^[0-9]+$'
ORDER BY int_age DESC
LIMIT 10;

edited Aug 15 '17 at 10:25

answered Aug 15 '17 at 10:17

Michael

5,095
2
13
35

Thank you. Perfect. – Dumarest Aug 15 '17 at 10:26

Selecting and sorting numbers from mysql varchar column

1 Answers1