mysqlordinals

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

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

Solution

  • 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;