如何在PostgreSQL中将数字单词转换为整数（如'four'转'4'）

当然可以，下面提供几种比冗长CASE语句更简洁、易维护的实现方式：

方法1：创建映射表+JOIN查询 #

通过建立数字单词与数值的映射表，后续只需通过JOIN完成转换，维护成本极低：

-- 创建持久化映射表
CREATE TABLE number_word_map (
    word VARCHAR(20) PRIMARY KEY,
    num INT NOT NULL UNIQUE
);

-- 插入1-50的数字单词数据
INSERT INTO number_word_map (word, num) VALUES
('one', 1), ('two', 2), ('three', 3), ('four', 4), ('five', 5),
('six', 6), ('seven', 7), ('eight', 8), ('nine', 9), ('ten', 10),
('eleven', 11), ('twelve', 12), ('thirteen', 13), ('fourteen', 14), ('fifteen', 15),
('sixteen', 16), ('seventeen', 17), ('eighteen', 18), ('nineteen', 19), ('twenty', 20),
('twenty-one', 21), ('twenty-two', 22), ('twenty-three', 23), ('twenty-four', 24), ('twenty-five', 25),
('twenty-six', 26), ('twenty-seven', 27), ('twenty-eight', 28), ('twenty-nine', 29), ('thirty', 30),
('thirty-one', 31), ('thirty-two', 32), ('thirty-three', 33), ('thirty-four', 34), ('thirty-five', 35),
('thirty-six', 36), ('thirty-seven', 37), ('thirty-eight', 38), ('thirty-nine', 39), ('forty', 40),
('forty-one', 41), ('forty-two', 42), ('forty-three', 43), ('forty-four', 44), ('forty-five', 45),
('forty-six', 46), ('forty-seven', 47), ('forty-eight', 48), ('forty-nine', 49), ('fifty', 50);

-- 实际转换示例
SELECT t.input_word, m.num
FROM your_target_table t
LEFT JOIN number_word_map m ON t.input_word = m.word;

如果只是临时使用，也可以用临时表代替持久化表。

方法2：JSONB映射函数 #

不想创建表的话，可以把映射关系封装在函数里，通过JSONB快速取值：

CREATE OR REPLACE FUNCTION word_to_num(word VARCHAR) RETURNS INT AS $$
DECLARE
    num_map JSONB := '{
        "one":1, "two":2, "three":3, "four":4, "five":5,
        "six":6, "seven":7, "eight":8, "nine":9, "ten":10,
        "eleven":11, "twelve":12, "thirteen":13, "fourteen":14, "fifteen":15,
        "sixteen":16, "seventeen":17, "eighteen":18, "nineteen":19, "twenty":20,
        "twenty-one":21, "twenty-two":22, "twenty-three":23, "twenty-four":24, "twenty-five":25,
        "twenty-six":26, "twenty-seven":27, "twenty-eight":28, "twenty-nine":29, "thirty":30,
        "thirty-one":31, "thirty-two":32, "thirty-three":33, "thirty-four":34, "thirty-five":35,
        "thirty-six":36, "thirty-seven":37, "thirty-eight":38, "thirty-nine":39, "forty":40,
        "forty-one":41, "forty-two":42, "forty-three":43, "forty-four":44, "forty-five":45,
        "forty-six":46, "forty-seven":47, "forty-eight":48, "forty-nine":49, "fifty":50
    }'::JSONB;
BEGIN
    RETURN (num_map ->> word)::INT;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL; -- 遇到不存在的单词返回NULL，可根据需求调整
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 调用示例
SELECT word_to_num('fifteen') AS num; -- 返回15
SELECT word_to_num('thirty-seven') AS num; -- 返回37

方法3：CTE临时映射（一次性查询用） #

如果只是临时转换，不需要持久化任何对象，可以用CTE构建映射后直接JOIN：

WITH number_map AS (
    SELECT 
        UNNEST(ARRAY[
            'one','two','three','four','five','six','seven','eight','nine','ten',
            'eleven','twelve','thirteen','fourteen','fifteen','sixteen','seventeen','eighteen','nineteen','twenty',
            'twenty-one','twenty-two','twenty-three','twenty-four','twenty-five','twenty-six','twenty-seven','twenty-eight','twenty-nine','thirty',
            'thirty-one','thirty-two','thirty-three','thirty-four','thirty-five','thirty-six','thirty-seven','thirty-eight','thirty-nine','forty',
            'forty-one','forty-two','forty-three','forty-four','forty-five','forty-six','forty-seven','forty-eight','forty-nine','fifty'
        ]) AS word,
        UNNEST(ARRAY[1,2,3,4,5,6,7,8,9,10,
            11,12,13,14,15,16,17,18,19,20,
            21,22,23,24,25,26,27,28,29,30,
            31,32,33,34,35,36,37,38,39,40,
            41,42,43,44,45,46,47,48,49,50]) AS num
)
SELECT t.input_word, m.num
FROM your_target_table t
LEFT JOIN number_map m ON t.input_word = m.word;

内容的提问来源于stack exchange，提问作者moonshot