8 Replies Latest reply: Feb 18, 2013 6:01 AM by BluShadow RSS

    Split the data type length

    936666
      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
          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
            Hopefully no comments on columns/table .. :)

            And this does not seem to be an ORACLE table..
            • 3. Re: Split the data type length
              APC
              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
                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
                  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
                    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
                      Thanks it works like a charm for me.
                      • 8. Re: Split the data type length
                        BluShadow
                        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