3 Replies Latest reply on Jan 4, 2013 7:03 PM by Purvesh K

    Seperate the string value


      this is my string

      varchar2(4000) :='Col1 number(4),col2 varchar2(20),col3 float, col4 number(10), col5 varchar2(30)........'

      i need as
      Col1 , col2 , col3, col4, col5.....

      how to proceed here

        • 1. Re: Seperate the string value

          Is it what you are looking for ?
          with data as (
          select 'Col1 number(4),col2 varchar2(20),col3 float, col4 number(10), col5 varchar2(30)' as str from dual
          ) select  regexp_replace(str, ' ([A-Za-z0-9\(\)]*,)| [A-Za-z0-9\(\)]*$',',') result_str
          from data;
          Col1,col2,col3, col4, col5,
          • 2. Re: Seperate the string value
            Another possibility would be to explore DBMS_UTILITY.TABLE_TO_COMMA.

            Demo here:
            • 3. Re: Seperate the string value
              Purvesh K
              One another way of doing it:
              with data as
                select 'Col1 number(4),col2 varchar2(20),col3 float, col4 number(10), col5 varchar2(30)' col from dual
              get_col_names as
                select col,
                       regexp_replace(col, '(.*? )(varchar2|number|float)(\([[:digit:]]+\))?', '\1') col_name
                  from data
              select col,
                     trim(regexp_substr(col_name, '[^,]+', 1, level)) col_n
                from get_col_names
              connect by col = prior col
                     and level <= regexp_count(col_name, ',')
                     and prior sys_guid() is not null;
              OL                                                                             COL_N                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
              ------------------------------------------------------------------------------- -------------------------------------
              Col1 number(4),col2 varchar2(20),col3 float, col4 number(10), col5 varchar2(30) Col1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
              Col1 number(4),col2 varchar2(20),col3 float, col4 number(10), col5 varchar2(30) col2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
              Col1 number(4),col2 varchar2(20),col3 float, col4 number(10), col5 varchar2(30) col3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
              Col1 number(4),col2 varchar2(20),col3 float, col4 number(10), col5 varchar2(30) col4