Oracle SQL实现HHMMSS格式字符串转时分标识格式的方法

需要将RowA列的数字（代表无前置零的HHMMSS格式，如21788对应02:17:88、2278对应00:22:78等）转换为忽略秒数的友好格式，规则如下：

• 小时数>0时，显示XhrYmin（如21788→2hr17min、241740→24hr17min）
• 小时数=0时，仅显示Ymin（如2278→22min、278→2min）

原SQL直接按字符串长度截取，未处理不同长度数字的前置零补全，导致截取位置混乱，无法适配所有场景。以下是正确解决方案：

基础实现 #

先统一将数字补全为6位字符串（补前置零），再拆分小时和分钟，通过CASE逻辑拼接结果：

SELECT
  CASE
    -- 判断小时部分是否大于0
    WHEN TO_NUMBER(SUBSTR(LPAD(TO_CHAR(ROWA), 6, '0'), 1, 2)) > 0 THEN
      TO_NUMBER(SUBSTR(LPAD(TO_CHAR(ROWA), 6, '0'), 1, 2)) || 'hr' || 
      TO_NUMBER(SUBSTR(LPAD(TO_CHAR(ROWA), 6, '0'), 3, 2)) || 'min'
    ELSE
      -- 小时为0时仅显示分钟
      TO_NUMBER(SUBSTR(LPAD(TO_CHAR(ROWA), 6, '0'), 3, 2)) || 'min'
  END AS formatted_time
FROM your_table;

代码说明 #

• LPAD(TO_CHAR(ROWA), 6, '0')：将RowA数字转为字符串，并补前置零到6位，确保统一为HHMMSS格式（如278→000278、21788→021788）
• SUBSTR(padded_str, 1, 2)：提取补全字符串的前2位（小时部分），转数字后判断是否大于0
• SUBSTR(padded_str, 3, 2)：提取第3-4位（分钟部分），转数字自动去除前置零（如02→2）

优化版本（避免重复计算） #

若需多次使用补全后的字符串，可通过CTE简化代码：

WITH padded_data AS (
  SELECT
    ROWA,
    LPAD(TO_CHAR(ROWA), 6, '0') AS padded_str
  FROM your_table
)
SELECT
  ROWA,
  CASE
    WHEN TO_NUMBER(SUBSTR(padded_str, 1, 2)) > 0 THEN
      TO_NUMBER(SUBSTR(padded_str, 1, 2)) || 'hr' || 
      TO_NUMBER(SUBSTR(padded_str, 3, 2)) || 'min'
    ELSE
      TO_NUMBER(SUBSTR(padded_str, 3, 2)) || 'min'
  END AS formatted_time
FROM padded_data;

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