This discussion is archived
8 Replies Latest reply: Feb 18, 2013 4:01 AM by BluShadow RSS

Split the data type length

936666 Newbie
Currently Being Moderated
How to split the data type length
say the string is

'Empid number(20) ,Empname varchar2(200),EmpSalary float, isactive boolean'

Need the output like
number,varchar2,float,boolean

Thanks!
  • 1. Re: Split the data type length
    APC Oracle ACE
    Currently Being Moderated
    It will be a regex of some description. Perhaps this will do the trick:
          regexp_replace('Empid number(20) ,Empname varchar2(200),EmpSalary float, isactive boolean'
                               , '([a-z]+) ([a-z]+)([0-9\(\]+)?(,?)'
                              , '\2\4' )
    If your column names contain underscores or numbers you will need to adjust the search patterns. You may also need to tweak the patterns for varying numbers of spaces.

    Cheers, APC

    Edited by: APC on Feb 18, 2013 10:09 AM
  • 2. Re: Split the data type length
    jeneesh Guru
    Currently Being Moderated
    Hopefully no comments on columns/table .. :)

    And this does not seem to be an ORACLE table..
  • 3. Re: Split the data type length
    APC Oracle ACE
    Currently Being Moderated
    jeneesh wrote:
    And this does not seem to be an ORACLE table..
    Not sure: VARCHAR2 says it is, BOOLEAN says it ain't :-/

    Cheers, APC
  • 4. Re: Split the data type length
    BluShadow Guru Moderator
    Currently Being Moderated
    Or like this...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (select 'Empid number(20) ,Empname varchar2(200),EmpSalary float, isactive boolean' as str from dual)
      2  --
      3  select regexp_replace(regexp_replace(regexp_replace(str,' ?, ?',','), '(^|,)[^ ]+ ([^ ,$]+)','\1\2'),'\(.*?\)') as new_str
      4* from t
    SQL> /
    
    NEW_STR
    -----------------------------
    number,varchar2,float,boolean
  • 5. Re: Split the data type length
    APC Oracle ACE
    Currently Being Moderated
    Hmmm, I think my version scores on readability (which is a relative concept when it comes to regex) but your solution returns VARCHAR2 whereas mine only gives VARCHAR.

    So I suppose Blu wins :)

    Cheers, APC
  • 6. Re: Split the data type length
    BluShadow Guru Moderator
    Currently Being Moderated
    APC wrote:
    Hmmm, I think my version scores on readability (which is a relative concept when it comes to regex) but your solution returns VARCHAR2 whereas mine only gives VARCHAR.

    So I suppose Blu wins :)
    :D

    Well, here's another variant... still as (un)readable, but only 2 regular expressions used (ok some other functions too, but you can't have everything)...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (select 'Empid number(20) ,Empname varchar2(200),EmpSalary float, isactive boolean' as str from dual)
      2  --
      3  select replace(trim(',' from regexp_replace(regexp_replace(str||',','\(.*?\)'),'[^ ]+ ([^ ,]+)([, ]+)','\1\2')),' ') as new_str
      4* from t
    SQL> /
    
    NEW_STR
    -----------------------------
    number,varchar2,float,boolean
  • 7. Re: Split the data type length
    936666 Newbie
    Currently Being Moderated
    Thanks it works like a charm for me.
  • 8. Re: Split the data type length
    BluShadow Guru Moderator
    Currently Being Moderated
    And another alternative if you fancy...
    SQL> ed
    Wrote file afiedt.buf
    
      1  with t as (select 'Empid number(20) ,Empname varchar2(200),EmpSalary float, isactive boolean' as str from dual)
      2  --
      3  select listagg(regexp_replace(substr(rw,instr(rw,' ')+1),'\(.*\)'),',') within group (order by rn) as result
      4  from (
      5        select level rn, trim(regexp_substr(str, '[^,]+', 1, level)) as rw
      6        from t
      7        connect by regexp_substr(str, '[^,]+', 1, level) is not null
      8*      )
    SQL> /
    
    RESULT
    ----------------------------------------------------------------------------------------------------------------------
    number,varchar2,float,boolean

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points