从基数10到基数26仅带字母，因此26将为aa [英] From base 10 to base 26 only with letters so that 26 will be aa

问题描述

我在Mysql中具有CONV功能:

I have the CONV function in Mysql:

mysql> CONV(26,10,26)
      -> 10

但是我想要这样的东西:

But I want something like this:

mysql> CONV_LETTERS(26,10,26)
      -> aa

我如何才能在MySQL中将基数从10转换为26，而只能使用字母使数字26变为aa?

更新

我到目前为止:

delimiter //
CREATE PROCEDURE `base10_to_base26_letters`(IN `nr` BIGINT)
BEGIN

SET @letters='abcdefghijklmnopqrstuvwxyz';
select SUBSTRING(@letters,`nr`+1,1);

END//

更新2

尝试将此 php函数转换为MySQL过程.

Trying to convert this php function to MySQL procedure.

PHP函数:

function generateAlphabet($na) {
    $sa = "";
    while ($na >= 0) {
        $sa = chr($na % 26 + 65) . $sa;
        $na = floor($na / 26) - 1;
    }
    return $sa;
}

到目前为止，我的MySQL过程:

My MySQL procedure so far:

delimiter //
CREATE PROCEDURE `base10_to_base26_letters`(IN `nr` BIGINT)
BEGIN

SET @n=`nr`,@letters='abcdefghijklmnopqrstuvwxyz',@r='';
while @n>=0 do
set @n=@n/26-1,@r=concat(SUBSTRING(@letters,@n%26,1),@r);
end while;
select @r;

END//

为什么我只为尝试输入的任何数字获得z?

Why I only get z for any number I tried?

推荐答案

此存储的函数将单个int转换为base26:

This stored function converts a single int to base26:

DROP FUNCTION IF EXISTS `base10to26`;
DELIMITER ;;

CREATE FUNCTION `base10to26`(`theNumber` INT(11) UNSIGNED) 
RETURNS VARCHAR(20) READS SQL DATA
BEGIN
    DECLARE colTxt VARCHAR(20);
    DECLARE value  INT(11);
    SET colTxt = '';

    REPEAT
        SET value  = theNumber % 26;
        IF value = 0 THEN SET value = 26; END IF;
        SET theNumber = (theNumber - value) / 26;
        SET colTxt = CONCAT(CHAR(value+64), colTxt);
    UNTIL theNumber = 0 END REPEAT;

    RETURN colTxt;
END;;
DELIMITER ;

这篇关于从基数10到基数26仅带字母，因此26将为aa的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！