Does mysql have function for returning a number with ordinal suffix?

Question

Basically I'm looking for something like

SELECT ordinal(my_number) FROM my_table

which would return

1st
11th
1071st
...
etc

but preferrably without the use of a stored procedure

Answer 1

mysql doesn't have support for this. You'll have to handle the strings in whichever language you are getting the mysql data from.

Answer 2

It is possible in MySQL using the string functions but it gets messy real fast. You'd better just do the suffix in the language you're using. For example, in PHP you could do something like this:

function ordSuffix($num) {
    if(empty($num) || !is_numeric($num) || $num == 0) return $num;
    $lastNum = substr($num, -1);
    $suffix = 'th';
    if($lastNum == 1 && $num != 11) { $suffix = 'st'; }
    elseif($lastNum == 2 && $num != 12) { $suffix = 'nd'; }
    elseif($lastNum == 3 && $num != 13) { $suffix = 'rd'; }
    return $num.$suffix;
}

echo ordSuffix(4); // 4th
echo ordSuffix(1); // 1st
echo ordSuffix(12); // 12th
echo ordSuffix(1052); // 1052nd

Answer 3

I don't know of a built-in function but it's pretty easy to write:

SELECT
  CONCAT(my_number, CASE
    WHEN my_number%100 BETWEEN 11 AND 13 THEN "th"
    WHEN my_number%10 = 1 THEN "st"
    WHEN my_number%10 = 2 THEN "nd"
    WHEN my_number%10 = 3 THEN "rd"
    ELSE "th"
  END)
FROM my_table;

Answer 4

I found a way that works for me but its a bit of a hack

DATE_FORMAT(CONCAT('2010-01-', my_number), '%D')

That works because currently the number I'm looking at never gets above 25. But it doesn't generalize well so someone might be entertained by this:

CONCAT(
    IF(my_number % 100 BETWEEN 11 AND 13,
        FLOOR(my_number / 100),
        FLOOR(my_number / 10)),
    DATE_FORMAT(
        CONCAT('2010-01-', 
            IF(my_number % 100 BETWEEN 11 AND 13
                my_number % 100,
                my_number % 10)),
        '%D'))

But that's a lot of work just to get at the DATE_FORMAT functionality when Ken's code is simpler.