Split function in oracle to comma separated values with automatic sequence

Question

Need Split function which will take two parameters  string to split and delimiter to split the string and return a table with columns Id and Data And how to call Split function which will return a table with columns Id and Data  Id column will contain sequence and data column will contain data of the string  Eg   SELECT FROM Split  A B C D         Result Should be in below format    Id   Data  --   ----  1    A     2    B     3    C     4    D

User · Answer

There are multiple options  See Split single comma delimited string into rows in Oracle  You just need to add LEVEL in the select list as a column  to get the sequence number to each row returned  Or  ROWNUM would also suffice    Using any of the below SQLs  you could include them into a FUNCTION   INSTR in CONNECT BY clause    SQL  WITH DATA AS   2      SELECT  word1  word2  word3  word4  word5  word6  str FROM dual   3        4  SELECT trim regexp substr str           1  LEVEL   str   5  FROM DATA   6  CONNECT BY instr str       1  LEVEL - 1    0   7     STR ---------------------------------------- word1 word2 word3 word4 word5 word6  6 rows selected   SQL    REGEXP SUBSTR in CONNECT BY clause    SQL  WITH DATA AS   2      SELECT  word1  word2  word3  word4  word5  word6  str FROM dual   3        4  SELECT trim regexp substr str           1  LEVEL   str   5  FROM DATA   6  CONNECT BY regexp substr str            1  LEVEL  IS NOT NULL   7     STR ---------------------------------------- word1 word2 word3 word4 word5 word6  6 rows selected   SQL    REGEXP COUNT in CONNECT BY clause    SQL  WITH DATA AS   2          SELECT  word1  word2  word3  word4  word5  word6  str FROM dual   3            4      SELECT trim regexp substr str           1  LEVEL   str   5      FROM DATA   6      CONNECT BY LEVEL    Using XMLTABLE   SQL  WITH DATA AS   2      SELECT  word1  word2  word3  word4  word5  word6  str FROM dual   3        4  SELECT trim COLUMN VALUE  str   5    FROM DATA  xmltable         REPLACE str                         6    STR ------------------------------------------------------------------------ word1 word2 word3 word4 word5 word6  6 rows selected   SQL    Using MODEL clause    SQL  WITH t AS   2      3         SELECT  word1  word2  word3  word4  word5  word6  str   4         FROM   dual       5  model param AS   6      7         SELECT str AS orig str     8                      9                          str  10                                                              AS mod str    11                1                                             AS start pos    12                Length str                                    AS end pos    13                 Length str  - Length Replace str           1 AS element count    14                0                                             AS element no    15                ROWNUM                                        AS rn  16         FROM   t    17  SELECT   trim Substr mod str  start pos  end pos-start pos   str  18  FROM        19                  SELECT    20                  FROM   model param MODEL PARTITION BY  rn  orig str  mod str   21                  DIMENSION BY  element no   22                  MEASURES  start pos  end pos  element count   23                  RULES ITERATE  2000   24                  UNTIL  ITERATION NUMBER 1   element count 0    25                    start pos ITERATION NUMBER 1    instr cv mod str        1  cv element no     1   26                  end pos iteration number 1    instr cv mod str        1  cv element no    1       27  WHERE    element no    0  28  ORDER BY mod str    29           element no  30     STR ------------------------------------------ word1 word2 word3 word4 word5 word6  6 rows selected   SQL    You could also use DBMS UTILITY package provided by Oracle  It provides various utility subprograms  One such useful utility is COMMA TO TABLE procedure  which converts a comma-delimited list of names into a PL SQL table of names   Read DBMS UTILITY COMMA TO TABLE

User · Answer

If you need a function try this  First we ll create a type   CREATE OR REPLACE TYPE T TABLE IS OBJECT       Field1 int       Field2 VARCHAR 25     CREATE TYPE T TABLE COLL IS TABLE OF T TABLE      Then we ll create the function   CREATE OR REPLACE FUNCTION TEST RETURN TABLE RETURN T TABLE COLL     IS       l res coll T TABLE COLL        l index number      BEGIN       l res coll    T TABLE COLL          FOR i IN           WITH TAB AS            SELECT  1001  ID   A B C D E F  STR FROM DUAL           UNION           SELECT  1002  ID   D E F  STR FROM DUAL           UNION           SELECT  1003  ID   C E G  STR FROM DUAL                     SELECT id            SUBSTR STR  instr STR       1  lvl    1  instr STR       1  lvl   1  - instr STR       1  lvl  - 1  name         FROM             SELECT        STR        AS STR  id FROM TAB                          SELECT level AS lvl FROM dual CONNECT BY level  lt   100                     WHERE lvl  lt   LENGTH STR  - LENGTH REPLACE STR        - 1         ORDER BY ID  NAME        LOOP         IF i ID   1001 THEN           l res coll extend            l index    l res coll count            l res coll l index    T TABLE i ID  i name           END IF        END LOOP        RETURN l res coll      END          Now we can select from it   select   from table TEST RETURN TABLE        Output   SQL gt  select   from table TEST RETURN TABLE          FIELD1 FIELD2 ---------- -------------------------       1001 A       1001 B       1001 C       1001 D       1001 E       1001 F  6 rows selected    Obviously you d need to replace the WITH TAB AS    bit with where you would be getting your actual data from  Credit Credit

User · Answer

Here is how you could create such a table    SELECT LEVEL AS id  REGEXP SUBSTR  A B C D            1  LEVEL  AS data    FROM dual CONNECT BY REGEXP SUBSTR  A B C D            1  LEVEL  IS NOT NULL    With a little bit of tweaking  i e   replacing the   in      with a variable  you could write such a function to return a table

User · Answer

Try like below  select      split field column name 1          name1      split field column name 2          name2 from table name

User · Answer

Oracle Setup   CREATE OR REPLACE FUNCTION split String    i str    IN  VARCHAR2    i delim  IN  VARCHAR2 DEFAULT       RETURN SYS ODCIVARCHAR2LIST DETERMINISTIC AS   p result       SYS ODCIVARCHAR2LIST    SYS ODCIVARCHAR2LIST      p start        NUMBER 5     1    p end          NUMBER 5     c len CONSTANT NUMBER 5     LENGTH  i str      c ld  CONSTANT NUMBER 5     LENGTH  i delim    BEGIN   IF c len  gt  0 THEN     p end    INSTR  i str  i delim  p start        WHILE p end  gt  0 LOOP       p result EXTEND        p result  p result COUNT      SUBSTR  i str  p start  p end - p start          p start    p end   c ld        p end    INSTR  i str  i delim  p start        END LOOP      IF p start  lt   c len   1 THEN       p result EXTEND        p result  p result COUNT      SUBSTR  i str  p start  c len - p start   1        END IF    END IF    RETURN p result  END      Query  SELECT ROWNUM AS ID         COLUMN VALUE AS Data FROM   TABLE  split String   A B C D         Output   ID DATA -- ----  1 A  2 B  3 C  4 D

User · Answer

This function returns the nth part of input string MYSTRING  Second input parameter is separator ie   SEPARATOR OF SUBSTR  and the third parameter is Nth Part which is required    Note  MYSTRING should end with the separator   create or replace FUNCTION PK GET NTH PART MYSTRING VARCHAR2 SEPARATOR OF SUBSTR VARCHAR2 NTH PART NUMBER  RETURN VARCHAR2 IS NTH SUBSTR VARCHAR2 500   POS1 NUMBER 4   POS2 NUMBER 4   BEGIN IF NTH PART 1 THEN SELECT REGEXP INSTR MYSTRING SEPARATOR OF SUBSTR  1  1   INTO POS1 FROM DUAL   SELECT SUBSTR MYSTRING 0 POS1-1  INTO NTH SUBSTR FROM DUAL  ELSE SELECT REGEXP INSTR MYSTRING SEPARATOR OF SUBSTR  1  NTH PART-1  INTO  POS1 FROM DUAL   SELECT REGEXP INSTR MYSTRING SEPARATOR OF SUBSTR  1  NTH PART   INTO POS2 FROM DUAL   SELECT SUBSTR MYSTRING POS1 1  POS2-POS1-1   INTO NTH SUBSTR FROM DUAL  END IF  RETURN NTH SUBSTR  END    Hope this helps some body  you can use this function like this in a loop to get all the values separated   SELECT REGEXP COUNT MYSTRING       1   i   INTO NO OF RECORDS FROM DUAL  WHILE NO OF RECORDS gt 0 LOOP     PK RECORD      PK GET NTH PART MYSTRING     NO OF RECORDS       -- do some thing     NO OF RECORDS    NO OF RECORDS-1  END LOOP    Here NO OF RECORDS PK RECORD are temp variables   Hope this helps

User · Answer

Best Query For comma separated  in This Query we Convert Rows To Column      SELECT listagg BL PRODUCT DESC        within    group    order by BL PRODUCT DESC  PROD   FROM GET PRODUCT --  WHERE BL PRODUCT DESC LIKE    WASH      WHERE Get Product Type Id   6000000000007

User · Answer

Use this  Split  function    CREATE OR REPLACE FUNCTION Split  p str varchar2  return sys refcursor is v res sys refcursor   begin   open v res for    WITH TAB AS     SELECT p str STR FROM DUAL    select substr STR  instr STR       1  lvl    1  instr STR       1  lvl   1  - instr STR       1  lvl  - 1  name    from       select        STR        as STR from TAB          select level as lvl from dual connect by level  lt   100       where lvl  lt   length STR  - length replace STR        - 1        return v res     end    You can t use this function in select statement like you described in question  but I hope you will find it still useful   EDIT  Here are steps you need to do   1  Create Object  create or replace type empy type as object value                   varchar2 512   2  Create Type  create or replace type t empty type as table of empy type 3  Create Function    CREATE OR REPLACE FUNCTION Split  p str varchar2  return sms t empty type is v emptype t empty type    t empty type    v cnt     number    0  v res sys refcursor  v value nvarchar2 128   begin   open v res for   WITH TAB AS    SELECT p str STR FROM DUAL    select substr STR  instr STR       1  lvl    1  instr STR       1  lvl       1  - instr STR       1  lvl  - 1  name   from       select        STR        as STR from TAB          select level as lvl from dual connect by level  lt   100       where lvl  lt   length STR  - length replace STR        - 1      loop      fetch v res into v value        exit when v res NOTFOUND        v emptype extend        v cnt    v cnt   1       v emptype v cnt     empty type v value       end loop      close v res       return v emptype  end    Then just call like this    SELECT   FROM  TABLE split  a b c d g

[oracle] Split function in oracle to comma separated values with automatic sequence

Examples related to oracle

Examples related to function

Examples related to plsql

Examples related to split

Examples related to delimiter