PostgreSQL：是否存在将基数为10的整数转换为基数为36的字符串的函数？ [英] PostgreSQL: Is there a function that will convert a base-10 int into a base-36 string?

问题描述

PostgreSQL中是否有一个函数可以将以10为底的数字（如 30 ）转换为以36为底的表示形式（如 u ？

Is there a function in PostgreSQL that can convert a base 10 number like 30 into a base 36 representation like u?

推荐答案

有base-64函数（例如 编码 ），但对于base- 36。但是您可以自己编写一个，也可以使用此：

There are base-64 functions (such as encode) but nothing for base-36. But you could write one of your own or use this one:

CREATE OR REPLACE FUNCTION base36_encode(IN digits bigint, IN min_width int = 0) RETURNS varchar AS $$
DECLARE
    chars char[]; 
    ret varchar; 
    val bigint; 
BEGIN
    chars := ARRAY['0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
    val := digits; 
    ret := ''; 
    IF val < 0 THEN 
        val := val * -1; 
    END IF; 
    WHILE val != 0 LOOP 
        ret := chars[(val % 36)+1] || ret; 
        val := val / 36; 
    END LOOP;

    IF min_width > 0 AND char_length(ret) < min_width THEN 
        ret := lpad(ret, min_width, '0'); 
    END IF;

    RETURN ret;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

我认为您应该问问自己，数据库是否是处理这种数据格式的正确位置但是，最好在接近堆栈的最终查看级别时处理此类表示性问题。

I think you should ask yourself if the database is the right place for dealing with this sort of data formatting though, presentational issues like this might be better handled closer to final viewing level of your stack.

这篇关于PostgreSQL：是否存在将基数为10的整数转换为基数为36的字符串的函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！