如何在Oracle SQL中将带分隔符的数字序列转换为二进制？

需要解析'1,3,4'、'1,6,7'这类逗号分隔的数字序列，转换为二进制字符串——序列中存在的数值对应位设为1，其余为0。例如'1,3,5'需转换为101010。

当前编写的查询语句无法正确合并二进制位，每个十进制值会生成单独的行，原SQL如下：

WITH split_values AS
(SELECT REGEXP_SUBSTR(values_to_split, '[^,]+', 1, level) AS splitted, temp_rowid
FROM (SELECT values_to_split, rowid temp_rowid FROM my_table
      WHERE values_to_split IS NOT NULL) temp_table
  GROUP BY REGEXP_SUBSTR(values_to_split, '[^,]+', 1, level), temp_rowid
  CONNECT BY REGEXP_SUBSTR(values_to_split, '[^,]+', 1, level) IS NOT NULL),
max_value AS
(SELECT MAX(splitted) max_part, temp_rowid FROM split_values
GROUP BY temp_rowid),
binary_zeros AS
(SELECT lpad('0', max_part + 1, '0') zero_binary_seq, temp_rowid from max_value)
SELECT regexp_replace(bz.zero_binary_seq,'.','1',2,sv.splitted) FROM binary_zeros bz
JOIN split_values sv ON sv.temp_rowid = bz.temp_rowid;

待处理的数值存储在my_table表的values_to_split列中。

修正方案1：字符串替换法 #

通过聚合拆分后的数字，再逐位替换生成二进制字符串：

WITH split_values AS (
    SELECT 
        REGEXP_SUBSTR(values_to_split, '[^,]+', 1, LEVEL) AS splitted_num,
        temp_rowid
    FROM (
        SELECT values_to_split, ROWID AS temp_rowid 
        FROM my_table 
        WHERE values_to_split IS NOT NULL
    ) temp_table
    CONNECT BY 
        REGEXP_SUBSTR(values_to_split, '[^,]+', 1, LEVEL) IS NOT NULL
        AND PRIOR temp_rowid = temp_rowid
        AND PRIOR SYS_GUID() IS NOT NULL -- 避免跨行生成重复数据
),
max_value AS (
    SELECT MAX(TO_NUMBER(splitted_num)) AS max_bit_pos, temp_rowid 
    FROM split_values 
    GROUP BY temp_rowid
),
base_binary AS (
    SELECT 
        LPAD('0', max_bit_pos + 1, '0') AS base_zero_str,
        temp_rowid
    FROM max_value
),
aggregated_bits AS (
    SELECT 
        temp_rowid,
        LISTAGG(splitted_num, ',') WITHIN GROUP (ORDER BY splitted_num) AS bit_positions
    FROM split_values
    GROUP BY temp_rowid
)
SELECT 
    REGEXP_REPLACE(
        b.base_zero_str,
        '(.)',
        CASE WHEN INSTR(',' || a.bit_positions || ',', ',' || LEVEL || ',') > 0 THEN '1' ELSE '\1' END,
        1,
        0
    ) AS result_binary
FROM base_binary b
JOIN aggregated_bits a ON b.temp_rowid = a.temp_rowid
CONNECT BY LEVEL <= LENGTH(b.base_zero_str)
    AND PRIOR b.temp_rowid = b.temp_rowid
    AND PRIOR SYS_GUID() IS NOT NULL
GROUP BY b.temp_rowid, b.base_zero_str, a.bit_positions;

关键修正点： #

• 在拆分逻辑中添加PRIOR条件，避免生成跨行重复的拆分结果
• 新增aggregated_bits将同一行的所有数字聚合为字符串，方便后续位判断
• 通过分层查询遍历二进制字符串的每一位，结合INSTR判断是否需要设为1，最后合并为单行结果

修正方案2：十进制聚合转二进制法 #

利用位运算聚合后直接转二进制，逻辑更简洁：

WITH split_values AS (
    SELECT 
        temp_rowid,
        POWER(2, TO_NUMBER(REGEXP_SUBSTR(values_to_split, '[^,]+', 1, LEVEL))) AS bit_value
    FROM (
        SELECT values_to_split, ROWID AS temp_rowid 
        FROM my_table 
        WHERE values_to_split IS NOT NULL
    ) temp_table
    CONNECT BY 
        REGEXP_SUBSTR(values_to_split, '[^,]+', 1, LEVEL) IS NOT NULL
        AND PRIOR temp_rowid = temp_rowid
        AND PRIOR SYS_GUID() IS NOT NULL
),
aggregated_decimal AS (
    SELECT 
        temp_rowid,
        SUM(bit_value) AS total_decimal
    FROM split_values
    GROUP BY temp_rowid
)
SELECT 
    TO_CHAR(total_decimal, 'FM' || LPAD('0', FLOOR(LOG(2, total_decimal)) + 1, '0')) AS result_binary
FROM aggregated_decimal;

逻辑说明： #

• 每个数字n对应2^n的十进制值，通过SUM聚合得到总十进制数
• 使用TO_CHAR将十进制数转为二进制字符串，FM去除前导空格，LPAD保证长度匹配最大位的位置

内容的提问来源于stack exchange，提问作者Сергей Башкинцев