1 2 Previous Next 19 Replies Latest reply: Jul 2, 2009 10:23 AM by 666352 RSS

    split function

    685758
      Hi ,

      select split('123,45,6,7',1,',') from dual

      ---------------------------------------------------------------------
      result 123
      ---------------------------------------------------------------------

      In above query only 1st value(123) will be in result .
      Is there any way to fetch all values from above query .
      I wana result like below in one query..help me.

      result
      123
      45
      6
      7
        • 1. Re: split function
          706055
          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 > 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;
          
          
          With this function, I can run a query like this: 
          
          SQL> select * from table(split('one,two,three'));
          Edited by: AJ99 on Jul 2, 2009 4:18 PM --- Remove 1 syntax Error.
          • 2. Re: split function
            BluShadow
            There's no need to create a function to split data.
            with t as (select 'aaaa,,bbbb,cccc,dddd,eeee,ffff' as txt from dual)
            -- end of sample data
            select REGEXP_SUBSTR (txt, '[^,]+', 1, level)
            from t
            connect by level <= length(regexp_replace(txt,'[^,]*'))+1
            
            REGEXP_SUBSTR(TXT,'[^;]+',1,LE
            ------------------------------
            aaaa
            bbbb
            cccc
            dddd
            eeee
            ffff
            
            
            7 rows selected.
            • 3. Re: split function
              489381
              More simpler
              SQL> select replace('123,45,6,7',',',chr(10)) from dual;
              
              REPLACE('1
              ----------
              123
              45
              6
              7
              I learnt from Frank :)
              • 4. Re: split function
                Frank Kulash
                Hi,
                pradeepcarya@gmail.com wrote:
                Hi ,

                select split('123,45,6,7',1,',') from dual

                ---------------------------------------------------------------------
                result 123
                ---------------------------------------------------------------------

                In above query only 1st value(123) will be in result .
                Is there any way to fetch all values from above query .
                Will
                select split('123,45,6,7',2,',') from dual 
                produce '45'?

                If so,
                SELECT     split ( '123,45,6,7'
                           , LEVEL
                           , ','
                           )
                FROM     dual
                CONNECT BY  LEVEL <= 1 + LENGTH ('123,45,6,7')
                                 - LENGTH ( REPLACE ( '123,45,6,7'
                                           , ','
                                           )
                                    );
                Or, instead of split, youu can use
                REGEXP_SUBSTR ( '123,45,6,7'
                           , '[^,]+'
                           , 1
                              , LEVEL
                           )
                • 5. Re: split function
                  Hoek
                  Hi Mbk,

                  Bare in mind that your example returns 1 record instead of 4...
                  • 6. Re: split function
                    539769
                    Here is other solution:
                    WITH t AS(select substr('123,45,6,7',1,instr('123,45,6,7',',',1,1)-1) STRING from dual UNION ALL
                    SELECT substr('123,45,6,7',instr('123,45,6,7',',',1)+1,instr('123,45,6,7',',',1,2)-instr('123,45,6,7',',',1)-1) FROM dual UNION ALL
                    SELECT substr('123,45,6,7',instr('123,45,6,7',',',1,2)+1,instr('123,45,6,7',',',1,3)-instr('123,45,6,7',',',1,2)-1) FROM dual UNION ALL
                    SELECT substr('123,45,6,7',instr('123,45,6,7',',',1,2)+1,instr('123,45,6,7',',',1,3)-instr('123,45,6,7',',',1,2)-1) FROM dual UNION ALL
                    SELECT substr('123,45,6,7',instr('123,45,6,7',',',1,3)+1) FROM dual)
                    SELECT * FROM t

                    Regards.
                    • 7. Re: split function
                      489381
                      Yes Mr.hoke you are correct,I checked it now
                      SQL> create table test(a varchar2(10));
                      
                      Table created.
                      
                      Elapsed: 00:00:00.85
                      SQL> insert into test
                        2  select replace('123,45,6,7',',',chr(10)) from dual;
                      
                      1 row created.
                      SQL> select * from test;
                      
                      A
                      ----------
                      123
                      45
                      6
                      7
                      
                      
                      Elapsed: 00:00:00.07
                      SQL> select count(*) from test;
                      
                        COUNT(*)
                      ----------
                               1
                      
                      Elapsed: 00:00:00.01
                      Thanks for your information
                      • 8. Re: split function
                        BluShadow
                        orant575 wrote:
                        Here is other solution:
                        WITH t AS(select substr('123,45,6,7',1,instr('123,45,6,7',',',1,1)-1) STRING from dual UNION ALL
                        SELECT substr('123,45,6,7',instr('123,45,6,7',',',1)+1,instr('123,45,6,7',',',1,2)-instr('123,45,6,7',',',1)-1) FROM dual UNION ALL
                        SELECT substr('123,45,6,7',instr('123,45,6,7',',',1,2)+1,instr('123,45,6,7',',',1,3)-instr('123,45,6,7',',',1,2)-1) FROM dual UNION ALL
                        SELECT substr('123,45,6,7',instr('123,45,6,7',',',1,2)+1,instr('123,45,6,7',',',1,3)-instr('123,45,6,7',',',1,2)-1) FROM dual UNION ALL
                        SELECT substr('123,45,6,7',instr('123,45,6,7',',',1,3)+1) FROM dual)
                        SELECT * FROM t
                        Well, that's great... but... what if there's another value in the string? e.g. '123,45,6,7,8' ?
                        • 9. Re: split function
                          539769
                          BluShadow,

                          Obviously that is limitation but if you are on release prior to 10g then this is efficient way to accomplish the job.

                          Regards.
                          • 10. Re: split function
                            MichaelS
                            In 11g
                            SQL>  var str varchar2(40)
                            
                            SQL>  exec :str := '123,45,6,7'
                            PL/SQL procedure successfully completed.
                            
                            SQL>  select * from xmltable((:str))
                            /
                            COLUMN_
                            -------
                            123    
                            45     
                            6      
                            7      
                            
                            4 rows selected.
                            • 11. Re: split function
                              ascheffer
                              And on a 11G database
                              select regexp_substr( str, '([^,]*)[,]{0,1}', 1, level, 'i', 1 ) part
                              from ( select '123,45,6,7' str from dual )
                              connect by level <= regexp_count( str, '[,]' ) + 1
                              Anton

                              P.S. @BluShaodow 7 rows selected?
                              • 12. Re: split function
                                Billy~Verreynne
                                create or replace type split_tbl as table of varchar2(32767);
                                /
                                show errors;
                                Invalid. The Oracle SQL engine does not support 32767 bytes varchar2 variables. And despite this definition not generating an error, the resulting SQL data type cannot handle varchar2 values larger than 4000 bytes.
                                • 13. Re: split function
                                  BluShadow
                                  orant575 wrote:
                                  BluShadow,

                                  Obviously that is limitation but if you are on release prior to 10g then this is efficient way to accomplish the job.
                                  Efficient? You think so?

                                  If someone provided a list of 50 values, you're going to have to write a hell of a lot of SQL to accomplish it the way you were doing it.

                                  In versions < 10g you could do something like:
                                  select substr( the_string
                                               , decode( level, 1, 1, instr(the_string,',',1,level-1)+1)
                                               , decode( instr(the_string,',',1,level), 0, length(the_string), instr(the_string,',',1,level) - decode( level, 1, 0, instr(the_string,',',1,level-1))-1)
                                               ) the_value
                                  from ( select '10,aaaa,20,vvvvv,30,xxx''xx,12,12,56' the_string
                                         from dual)
                                  connect by level <= length(the_string)-length(replace(the_string,','))+1
                                  
                                  THE_VALUE
                                  -----------------------------------
                                  10
                                  aaaa
                                  20
                                  vvvvv
                                  30
                                  xxx'xx
                                  12
                                  12
                                  56
                                  
                                  9 rows selected.
                                  • 14. Re: split function
                                    BluShadow
                                    ascheffer wrote:
                                    P.S. @BluShaodow 7 rows selected?
                                    Yes Anton, 7 rows.

                                    If you looked at my test data
                                    'aaaa,,bbbb,cccc,dddd,eeee,ffff'
                                    There is a null value given between the aaaa and bbbb values.
                                    That also, perfectly validly, comes out as a value in the result set.

                                    ;)
                                    1 2 Previous Next