Split varchar into separate columns in Oracle

Question

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

User · Answer

With REGEXP SUBSTR is as simple as   SELECT REGEXP SUBSTR t column one           1  1  col one         REGEXP SUBSTR t column one           1  2  col two FROM YOUR TABLE t

User · Answer

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     Reference    SUBSTR INSTR

User · Answer

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  gt  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

[sql] Split varchar into separate columns in Oracle

Examples related to sql

Examples related to oracle