在Oracle中将varchar分割为单独的列

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?

如果只为了将一个字符串分割成两个，那么是否可以定义一个列?

3 个解决方案

#1

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+支持regex，允许根据需要解决的情况提供更大的灵活性。它还有一个regex子字符串方法…

Reference:

参考:

SUBSTR
字符串的子串
INSTR
INSTR

#2

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;

#3

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;

#1