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

    Seperate the string value

    936666
    936666 Jan 4, 2013 10:32 AM
      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!
      • 120 Views
      • Tags:
        • 1. Re: Seperate the string value
          Troll35
          Troll35 Jan 4, 2013 3:43 PM (in response to 936666)
          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
            damorgan Jan 4, 2013 4:50 PM (in response to Troll35)
            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
              Purvesh K Jan 4, 2013 7:03 PM (in response to 936666)
              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