SELECT param_value,
CASE pos1
WHEN 0
THEN param_value
ELSE SUBSTR(param_value, 1, pos1 - 1)
END AS param_value1,
CASE
WHEN pos1 = 0
THEN NULL
WHEN pos2 = 0
THEN SUBSTR(param_value, pos1 + 1)
ELSE SUBSTR(param_value, pos1 + 1, pos2 - pos1 - 1)
END AS param_value2,
CASE
WHEN pos2 = 0
THEN NULL
WHEN pos3 = 0
THEN SUBSTR(param_value, pos2 + 1)
ELSE SUBSTR(param_value, pos2 + 1, pos3 - pos2 - 1)
END AS param_value3,
CASE
WHEN pos3 = 0
THEN NULL
WHEN pos4 = 0
THEN SUBSTR(param_value, pos3 + 1)
ELSE SUBSTR(param_value, pos3 + 1, pos4 - pos3 - 1)
END AS param_value4
FROM (
SELECT param_value,
INSTR(param_value, ',', 1, 1) AS pos1,
INSTR(param_value, ',', 1, 2) AS pos2,
INSTR(param_value, ',', 1, 3) AS pos3,
INSTR(param_value, ',', 1, 4) AS pos4,
INSTR(param_value, ',', 1, 5) AS pos5
FROM table_name
);
或者,使用正则表达式,更简洁但速度更慢:
SELECT param_value,
REGEXP_SUBSTR(param_value, '([^,]*)(,|$)', 1, 1, NULL, 1) AS param_value1,
REGEXP_SUBSTR(param_value, '([^,]*)(,|$)', 1, 2, NULL, 1) AS param_value2,
REGEXP_SUBSTR(param_value, '([^,]*)(,|$)', 1, 3, NULL, 1) AS param_value3,
REGEXP_SUBSTR(param_value, '([^,]*)(,|$)', 1, 4, NULL, 1) AS param_value4
FROM table_name;
其中,对于示例数据:
create table table_name (param_value) AS
SELECT NULL FROM DUAL UNION ALL
SELECT 'A' FROM DUAL UNION ALL
SELECT 'A,B' FROM DUAL UNION ALL
SELECT 'A,B,C' FROM DUAL UNION ALL
SELECT 'A,B,C,D' FROM DUAL UNION ALL
SELECT 'A,B,C,D,E' FROM DUAL;
SELECT param_value,
item,
CASE epos
WHEN 0
THEN SUBSTR(param_value, spos)
ELSE SUBSTR(param_value, spos, epos - spos)
END AS param
FROM table_name t
CROSS APPLY (
SELECT LEVEL AS item,
CASE LEVEL
WHEN 1
THEN 1
ELSE INSTR(t.param_value, ',', 1, LEVEL - 1) + 1
END AS spos,
INSTR(t.param_value, ',', 1, LEVEL) AS epos
FROM DUAL
CONNECT BY INSTR(t.param_value, ',', 1, LEVEL - 1) > 0
) b
其中,对于示例数据:
create table table_name (param_value) AS
SELECT NULL FROM DUAL UNION ALL
SELECT 'A' FROM DUAL UNION ALL
SELECT 'A,B' FROM DUAL UNION ALL
SELECT 'A,B,C' FROM DUAL UNION ALL
SELECT 'A,B,C,D' FROM DUAL UNION ALL
SELECT 'A,B,C,D,E' FROM DUAL;
2条答案
按热度按时间inb24sb21#
SQL(不仅仅是Oracle)需要已知的固定输出列数。因此,如果字符串中有固定的最大分隔值数,则可以使用字符串函数来查找子字符串:
或者,使用正则表达式,更简洁但速度更慢:
其中,对于示例数据:
两者输出:
| 参数值|参数_值1|参数_值2|参数_值3|参数_值4|
| - -|- -|- -|- -|- -|
| * 空值 | 空值 | 空值 | 空值 | 空值 *|
| A级|A级| * 空值 | 空值 | 空值 *|
| A、B| A级|B| * 空值 | 空值 *|
| A、B、C| A级|B| C类| * 空值 *|
| A、B、C、D| A级|B| C类|D级|
| A、B、C、D、E| A级|B| C类|D级|
fiddle
lfapxunr2#
如果要为未知长度的列表生成值,请将值生成为行(而不是列):
其中,对于示例数据:
输出:
| 参数值|项目|参数|
| - -|- -|- -|
| * 空值 *| 一个| * 空值 *|
| A级|一个|A级|
| A、B|一个|A级|
| A、B| 2个|B|
| A、B、C|一个|A级|
| A、B、C| 2个|B|
| A、B、C|三个|C类|
| A、B、C、D|一个|A级|
| A、B、C、D| 2个|B|
| A、B、C、D|三个|C类|
| A、B、C、D|四个|D级|
| A、B、C、D、E|一个|A级|
| A、B、C、D、E| 2个|B|
| A、B、C、D、E|三个|C类|
| A、B、C、D、E|四个|D级|
| A、B、C、D、E|五个|E级|
如果您希望将其作为列,则可以在用于访问数据库的任何第三方应用程序中转置数据集,因为它可能支持您希望的输出格式(而使用SQL生成动态输出将非常困难)。
fiddle