SQL 在 Oracle 中将 varchar 拆分为单独的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5199849/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Split varchar into separate columns in Oracle
提问by ryebr3ad
I'm in a bit of a pickle: I've been asked to take in comments starting with a specific string from a database, and separate the result into separate columns.
我有点不高兴：我被要求接受以数据库中特定字符串开头的注释，并将结果分成单独的列。
For example -- if a returned value is this:
例如——如果返回值是这样的：
COLUMN_ONE
--------------------
'D7ERROR username'
The return needs to be:
回报需要是：
COL_ONE COL_TWO
--------------------
D7ERROR username
Is it even possible to define columns once the result set has been structured just for the sake of splitting a string into two?
是否甚至可以在结果集结构化后定义列，只是为了将字符串分成两部分？
回答by OMG Ponies
Depends on the consistency of the data - assuming a single space is the separator between what you want to appear in column one vs two:
取决于数据的一致性 - 假设单个空格是您想要出现在第一列与第二列中的分隔符：
SELECT SUBSTR(t.column_one, 1, INSTR(t.column_one, ' ')-1) AS col_one,
SUBSTR(t.column_one, INSTR(t.column_one, ' ')+1) AS col_two
FROM YOUR_TABLE t
Oracle 10g+ has regex support, allowing more flexibility depending on the situation you need to solve. It also has a regex substring method...
Oracle 10g+ 支持正则表达式，根据您需要解决的情况提供更大的灵活性。它还有一个正则表达式子字符串方法......
Reference:
参考：
回答by talek
With REGEXP_SUBSTR is as simple as:
使用 REGEXP_SUBSTR 就像这样简单：
SELECT REGEXP_SUBSTR(t.column_one, '[^ ]+', 1, 1) col_one,
REGEXP_SUBSTR(t.column_one, '[^ ]+', 1, 2) col_two
FROM YOUR_TABLE t;
回答by bluesky
Simple way is to convert into column
简单的方法是转换成列
SELECT COLUMN_VALUE FROM TABLE (SPLIT ('19869,19572,19223,18898,10155,'))
CREATE TYPE split_tbl as TABLE OF VARCHAR2(32767);
CREATE OR REPLACE FUNCTION split (p_list VARCHAR2, p_del VARCHAR2 := ',')
RETURN split_tbl
PIPELINED IS
l_idx PLS_INTEGER;
l_list VARCHAR2 (32767) := p_list;
l_value VARCHAR2 (32767);
BEGIN
LOOP
l_idx := INSTR (l_list, p_del);
IF l_idx > 0 THEN
PIPE ROW (SUBSTR (l_list, 1, l_idx - 1));
l_list := SUBSTR (l_list, l_idx + LENGTH (p_del));
ELSE
PIPE ROW (l_list);
EXIT;
END IF;
END LOOP;
RETURN;
END split;
