8 Replies Latest reply: May 19, 2014 4:18 AM by Paul Horth RSS

    how to diplay data from delimated column

    879605

      Hello everybody ,

       

      I have 2 delimited columns ( item , quantity )

       

       

      Column_ITEM                                 Colum_QTY

      apple                                                9

      apple + banana + orange                1 + 5 + 90

      apple + orange + banana                 88 + 6 + 0

      banana + orange                              9  + 7

      orange + banana                              2 + 0

      orange + apple + banana                 0 + 0 + 76

      orange + banana + apple                 0 + 0 + 6

       

       

      I need to display one item a time with its corresponding quantity like below


      apple 9

      apple 1

      apple 88

      apple 0

      apple 6

       

       

       

      thanks for help

        • 1. Re: how to diplay data from delimated column
          Solomon Yakobson

          with t as (
                     select 'apple + banana + orange' item,'1 + 5 + 90' quantity from dual union all
                     select 'apple + orange + banana','88 + 6 + 0' from dual union all
                     select 'banana + orange','9  + 7' from dual union all
                     select 'orange + banana','2 + 0' from dual union all
                     select 'orange + apple + banana','0 + 0 + 76' from dual union all
                     select 'orange + banana + apple','0 + 0 + 6' from dual
                    )
          select  regexp_substr(item,' *([^+]+) *',1,column_value,null,1) item,
                  to_number(regexp_substr(quantity,' *([^+]+) *',1,column_value,null,1)) quantity
            from  t,
                  table(
                        cast(
                             multiset(
                                      select  level
                                        from  dual
                                        connect by level <= regexp_count(item,'\+') + 1
                                     )
                             as sys.OdciNumberList
                            )
                       )
          /


          ITEM                      QUANTITY
          ----------------------- ----------
          apple                            1
          banana                           5
          orange                          90
          apple                           88
          orange                           6
          banana                           0
          banana                           9
          orange                           7
          orange                           2
          banana                           0
          orange                           0

          ITEM                      QUANTITY
          ----------------------- ----------
          apple                            0
          banana                          76
          orange                           0
          banana                           0
          apple                            6

          16 rows selected.

          SCOTT@orcl >

           

          SY.

          • 2. Re: how to diplay data from delimated column
            chris227

            with testdata as (
                select 'apple + banana + orange' s, '1 + 5 + 90' n from dual union all
                select 'apple + orange + banana', '88 + 6 + 0' from dual union all
                select 'banana + orange' ,'9  + 7' from dual union all
                select 'orange + banana', '2 + 0' from dual union all
                select 'orange + apple + banana' ,'0 + 0 + 76' from dual union all
                select 'orange + banana + apple' , '0 + 0 + 6' from dual
            )

            select * from (
            select
                regexp_substr(s, '[^ +]+', 1, level) fruits
               ,regexp_substr(n, '[^ +]+', 1, level) cnt
               ,level
            from testdata
            connect by
                s = prior s
                and prior sys_guid() is not null
                and level <= regexp_count(s, '\+') + 1
            )
            where fruits = 'apple'

             

            FRUITS,CNT,LEVEL

            "apple","1","1"

            "apple","88","1"

            "apple","0","2"

            "apple","6","3"

            • 3. Re: how to diplay data from delimated column
              EdStevens

              I hope this is a homework problem designed to teach various techniques, because the very concept of a 'delimited column' flies in the face of proper data design and normalization.

              • 4. Re: how to diplay data from delimated column
                michaelrozar17

                Alternatively..

                 

                with t as (

                select 'apple + banana + orange' Column_ITEM, '1 + 5 + 90' Column_QTY from dual union all

                select 'apple + orange + banana', '88 + 6 +0' from dual union all

                select 'banana + orange',         '9  + 7' from dual union all

                select 'orange + banana',         '2 + 0' from dual union all

                select 'orange + apple + banana', '0 + 0 + 76' from dual union all

                select 'orange + banana + apple', '0 + 0 + 6'  from dual

                )

                select *

                    from

                    (select regexp_substr(column_item,'[^+ ]+',1,level) col_itm

                           ,regexp_substr(column_qty,'[^+ ]+',1,level) col_qty

                         from t

                            connect by column_item = prior column_item

                            and level<=regexp_count(column_item,'[^+ ]+')

                            and prior sys_guid () is not null

                    )

                    where col_itm='apple'

                • 5. Re: how to diplay data from delimated column
                  Paul  Horth

                  879605 wrote:

                   

                  Hello everybody ,

                   

                  I have 2 delimited columns ( item , quantity )

                   

                   

                  Column_ITEM                                 Colum_QTY

                  apple + banana + orange                1 + 5 + 90

                  apple + orange + banana                 88 + 6 + 0

                  banana + orange                              9  + 7

                  orange + banana                              2 + 0

                  orange + apple + banana                 0 + 0 + 76

                  orange + banana + apple                 0 + 0 + 6

                   

                   

                  I need to display one item a time with its corresponding quantity like below

                   

                  apple 1

                  apple 88

                  apple 0

                  apple 6

                   

                   

                   

                  thanks for help

                  That's not the question you should be asking. The question should be

                   

                  "Why am I storing my data in such a crazy fashion?"

                   

                  Data on a relational database should be in Third Normal Form

                  • 6. Re: how to diplay data from delimated column
                    879605

                    the same question I have asked to the database designer , be sure that there are some cases in the world it might required such design and I don't want to waste your time by explaining why we have to go with such design ,

                    keep open mind

                    • 7. Re: how to diplay data from delimated column
                      rp0428
                      the same question I have asked to the database designer , be sure that there are some cases in the world it might required such design and I don't want to waste your time by explaining why we have to go with such design ,

                      keep open mind

                      Aw, gee - go ahead and humor us.

                       

                      Tell us WHY you have to use such a design.

                      I need to display one item a time with its corresponding quantity like below

                      Now tell us WHY you can't use one of solutions posted here to PARSE and STORE the data 'one item at a time' to begin with?

                       

                      Why should anyone have to parse the data EVERY TIME they want to query it when you could just parse it once and store it properly?

                      • 8. Re: how to diplay data from delimated column
                        Paul  Horth

                        879605 wrote:

                         

                        the same question I have asked to the database designer , be sure that there are some cases in the world it might required such design and I don't want to waste your time by explaining why we have to go with such design ,

                        keep open mind

                        There are occasions when a violation of third normal form is acceptable (for performance reasons). This doesn't seem to be one of those cases.

                         

                        Remember there is a difference between keeping an open mind and letting your brains fall out.