3 Replies Latest reply: Jan 4, 2013 1:03 PM by Purvesh K RSS

    Seperate the string value

    936666
      hi,

      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

      Thanks!
        • 1. Re: Seperate the string value
          Troll35
          Hello,

          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;
          
          RESULT_STR
          ---------------------------
          Col1,col2,col3, col4, col5,
          Regards,
          • 2. Re: Seperate the string value
            damorgan
            Another possibility would be to explore DBMS_UTILITY.TABLE_TO_COMMA.

            Demo here:
            http://www.morganslibrary.org/reference/pkgs/dbms_utility.html#util35
            • 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