Is there a function to split a string in PL SQL

Question

I need to write a procedure to normalize a record that have multiple tokens concatenated by one char  I need to obtain these tokens splitting the string and insert each one as a new record in a table  Does Oracle have something like a  split  function

User · Answer

This only works in Oracle 10G and greater.

Basically, you use regex_substr to do a split on the string.

https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

User · Answer

There is apex util string to table - see my answer to this question     Also  prior to the existence of the above function  I once posted a solution here on my blog   Update  In later versions of APEX  apex util string to table is deprecated  and a similar function apex string split is preferred

User · Answer

There is a simple way folks   Use REPLACE function   Here is an example of comma separated string ready to be passed to IN clause   In PL SQL   StatusString      REPLACE  Active Completed                    In SQL Plus   Select  REPLACE  Active Completed                 from dual

User · Answer

If APEX UTIL is not available  you have a solution using REGEXP SUBSTR     Inspired from http   nuijten blogspot fr 2009 07 splitting-comma-delimited-string-regexp html    DECLARE   I INTEGER    TYPE T ARRAY OF VARCHAR IS TABLE OF VARCHAR2 2000  INDEX BY BINARY INTEGER    MY ARRAY T ARRAY OF VARCHAR    MY STRING VARCHAR2 2000      123 456 abc def   BEGIN   FOR CURRENT ROW IN       with test as            select MY STRING from dual        select regexp substr MY STRING           1  rownum  SPLIT       from test       connect by level  lt   length  regexp replace MY STRING               1    LOOP     DBMS OUTPUT PUT LINE CURRENT ROW SPLIT       MY ARRAY MY ARRAY COUNT     CURRENT ROW SPLIT    END LOOP  END

User · Answer

You can use regexp substr    Example   create or replace type splitTable Type is table of varchar2 100    declare     l split table splitTable Type  begin   select       regexp substr  SMITH ALLEN WARD JONES           1  level    bulk collect into       l split table   from dual   connect by       regexp substr  SMITH ALLEN WARD JONES            1  level  is not null  end    The query iterates through the comma separated string  searches for the comma     and then splits the string by treating the comma as delimiter  It returns the string as a row  whenever it hits a delimiter   level in statement regexp substr  SMITH ALLEN WARD JONES           1  level  refers to a pseudocolumn in Oracle which is used in a hierarchical query to identify the hierarchy level in numeric format  level in connect by

User · Answer

function numinstr p source in varchar2 p token in varchar2  return pls integer is     v occurrence pls integer    1      v start pls integer    1      v loc pls integer  begin     v loc  instr p source  p token  1  1       while v loc  gt  0 loop       v occurrence    v occurrence 1        v start  v loc 1        v loc  instr p source  p token  v start  1       end loop      return v occurrence-1  end numinstr    --   --   --   -- function get split field p source in varchar2 p delim in varchar2 nth pls integer  return varchar2 is     v num delims pls integer      first pos pls integer      final pos pls integer      len delim pls integer    length p delim       ret len pls integer  begin     v num delims    numinstr p source p delim       if nth  lt  1 or nth  gt  v num delims 1 then       return null      else       if nth   1 then         first pos    1        else         first pos    instr p source  p delim  1  nth-1    len delim        end if        if nth  gt  v num delims then         final pos    length p source         else         final pos    instr p source  p delim  1  nth  - 1        end if        ret len     final pos - first pos    1        return substr p source  first pos  ret len       end if  end get split field

User · Answer

Please find next an example you may find useful  --1st substring  select substr  alfa bravo charlie delta   1      instr  alfa bravo charlie delta        1  1 -1  from dual    --2nd substring  select substr  alfa bravo charlie delta   instr  alfa bravo charlie delta        1  1  1      instr  alfa bravo charlie delta        1  2  - instr  alfa bravo charlie delta        1  1  -1  from dual    --3rd substring  select substr  alfa bravo charlie delta   instr  alfa bravo charlie delta        1  2  1      instr  alfa bravo charlie delta        1  3  - instr  alfa bravo charlie delta        1  2  -1  from dual    --4th substring  select substr  alfa bravo charlie delta   instr  alfa bravo charlie delta        1  3  1  from dual    Best regards  Emanuele

User · Answer

I like the look of that apex utility   However its also good to know about the standard oracle functions you can use for this  subStr and inStr http   download oracle com docs cd B19306 01 server 102 b14200 functions001 htm

User · Answer

I needed a function that splits a clob and makes sure the function is usable in sql   create or replace type vchar tab is table of varchar2 4000      create or replace function split      p list in clob      p separator in varchar2 default       return vchar tab pipelined is     C SQL VCHAR MAX constant integer  4000      C MAX AMOUNT    constant integer  28000      C SEPARATOR LEN constant integer  length p separator       l amount        integer  C MAX AMOUNT      l offset        integer  1      l buffer        varchar2 C MAX AMOUNT       l list          varchar2 32767       l index         integer  begin     if p list is not null then         loop             l index  instr l list  p separator               if l index  gt  C SQL VCHAR MAX 1 then                 raise application error -20000   item is too large for sql varchar2  len     l index-1                elsif l index  gt  0 then -- found an item  pipe it                 pipe row  substr l list  1  l index-1                    l list  substr l list  l index C SEPARATOR LEN               elsif length l list   gt  C SQL VCHAR MAX then                 raise application error -20001   item is too large for sql varchar2  length exceeds    length l list                elsif l amount   C MAX AMOUNT then -- more to read from the clob                 dbms lob read p list  l amount  l offset  l buffer                   l list  l list  l buffer              else -- read through the whole clob                 if length l list   gt  0 then                     pipe row  l list                   end if                  exit              end if          end loop      end if       return  exception     when no data needed then -- this happens when you don t fetch all records         null  end      Test   select   from table split  ASDF IUYT KJHG ASYD

User · Answer

You could use a combination of SUBSTR and INSTR as follows    Example string   field    DE124028   1048708   000   536967136      The seperator being       To get the  1048708  for example     If the field is of fixed length   7 here      substr field instr field       1 1  3 7    If the field is of variable length    substr field instr field       1 1  3 instr field       1 2  -  instr field       1 1  3      You should probably look into SUBSTR and INSTR functions for more flexibility

User · Answer

You have to roll your own  E g       from  http   www builderau com au architect database soa Create-functions-to-join-and-split-strings-in-Oracle 0 339024547 339129882 00 htm  select split  foo bar zoo   from dual  select   from table split  foo bar zoo      pipelined function is SQL only  no PL SQL        create or replace type split tbl as table of varchar2 32767     show errors  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    show errors      An own implementation      create or replace function split2    list in varchar2    delimiter in varchar2 default       return split tbl as   splitted split tbl    split tbl      i pls integer    0    list  varchar2 32767     list  begin   loop     i    instr list   delimiter       if i  gt  0 then       splitted extend 1         splitted splitted last     substr list   1  i - 1         list     substr list   i   length delimiter        else       splitted extend 1         splitted splitted last     list         return splitted      end if    end loop  end    show errors  declare   got split tbl     procedure print tbl in split tbl  as   begin     for i in tbl first    tbl last loop       dbms output put line i             tbl i        end loop    end   begin   got    split2  foo bar zoo      print got     print split2  1 2 3 4 5          end

[string] Is there a function to split a string in PL/SQL?

Examples related to string

Examples related to oracle

Examples related to plsql

Examples related to split

Examples related to tokenize