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
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;
mysql doesn't have support for this. You'll have to handle the strings in whichever language you are getting the mysql data from.
Based on Ken's code, a custom MySQL function would be as follows:
DELIMITER $$
CREATE FUNCTION ordinal(number BIGINT)
RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
DECLARE ord VARCHAR(64);
SET ord = (SELECT CONCAT(number, CASE
WHEN number%100 BETWEEN 11 AND 13 THEN "th"
WHEN number%10 = 1 THEN "st"
WHEN number%10 = 2 THEN "nd"
WHEN number%10 = 3 THEN "rd"
ELSE "th"
END));
RETURN ord;
END$$
DELIMITER ;
Then it can be used as:
SELECT ordinal(1) -- 1st
SELECT ordinal(11) -- 11th
SELECT ordinal(21) -- 21st
SELECT ordinal(my_number) FROM my_table
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
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.
精彩评论