在PL/SQL中如何便捷获取数字字符串中的最大值？

针对不同数据库和应用场景，有多种无需手动遍历比较的简便方法：

数据库端处理 #

MySQL（8.0+） #

最简洁的方式是利用JSON_TABLE将字符串转成结构化数据后取最大值：

SELECT MAX(num) AS max_num
FROM your_table,
JSON_TABLE(
    CONCAT('[', your_num_str_field, ']'),
    '$[*]' COLUMNS(num INT PATH '$')
) AS jt;

也可以用递归CTE拆分字符串（兼容低版本）：

WITH RECURSIVE split_nums AS (
    SELECT 
        SUBSTRING_INDEX(your_num_str_field, ',', 1) AS num,
        SUBSTRING(your_num_str_field FROM LENGTH(SUBSTRING_INDEX(your_num_str_field, ',', 1)) + 2) AS rest
    FROM your_table
    WHERE your_num_str_field != ''
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(rest, ',', 1) AS num,
        SUBSTRING(rest FROM LENGTH(SUBSTRING_INDEX(rest, ',', 1)) + 2) AS rest
    FROM split_nums
    WHERE rest != ''
)
SELECT MAX(CAST(num AS UNSIGNED)) AS max_num FROM split_nums;

PostgreSQL #

直接将字符串转成数组后使用array_max函数：

SELECT array_max(string_to_array(your_num_str_field, ',')::INT[]) AS max_num FROM your_table;

或者拆分后聚合：

SELECT MAX(CAST(num AS INT)) AS max_num
FROM your_table, unnest(string_to_array(your_num_str_field, ',')) AS num;

SQL Server #

利用STRING_SPLIT函数拆分后取最大值：

SELECT MAX(CAST(value AS INT)) AS max_num
FROM your_table
CROSS APPLY STRING_SPLIT(your_num_str_field, ',');

Oracle（12c+） #

通过JSON函数将字符串转成结构化数据：

SELECT MAX(CAST(num AS INT)) AS max_num
FROM your_table,
JSON_TABLE(
    '["' || REPLACE(your_num_str_field, ',', '","') || '"]',
    '$[*]' COLUMNS(num INT PATH '$')
) AS jt;

应用层处理 #

如果在应用代码中处理，比如Python：

num_str = "50,40,60,10,0,4"
max_num = max(map(int, num_str.split(',')))

Java：

String numStr = "50,40,60,10,0,4";
int maxNum = Arrays.stream(numStr.split(","))
                   .mapToInt(Integer::parseInt)
                   .max()
                   .orElse(0);

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