10 Replies Latest reply: Jun 10, 2010 4:31 AM by Nicosa-Oracle RSS

    Order by Bulleted Numbers

    Selvaganapathy
      I have a column called reference number (Datatype - Varchar2) in one of the tables which as values, such as bulleted numbers Example as below

      1
      1.1
      1.1.1
      1.1.2.a
      1.1.2.b
      1.2

      Is there an easy way to order by the rows, by such a column which is a bulleted value.
        • 1. Re: Order by Bulleted Numbers
          Nimish Garg
          use simply order by

          as

          with t as(
          select '1' x from dual
          union
          select '1.1' from dual
          union
          select '1.1.1' from dual
          union
          select '1.1.2.a' from dual
          union
          select '1.1.2.b' from dual
          union
          select '1.2' from dual
          )
          select x from t order by x
          • 2. Re: Order by Bulleted Numbers
            Selvaganapathy
            Thanks for the quick help, but the challenge is it fails in cases like this one

            with t as(
            select '1' x from dual
            union
            select '1.1' from dual
            union
            select '1.1.1' from dual
            union
            select '1.1.2.a' from dual
            union
            select '1.1.2.b' from dual
            union
            select '1.2' from dual
            UNION
            Select '10.2.3' from dual
            UNION
            select '2.1.3' from dual
            )
            select x from t order by x
            • 4. Re: Order by Bulleted Numbers
              Nimish Garg
              then if u know how many dots (.) are there u may use substr/instr to get the seprated values and order by accordingly
              • 5. Re: Order by Bulleted Numbers
                Aketi Jyuuzou
                Order by comma separated number values???
                I say again that I like this regex book B-)
                http://oreilly.com/catalog/9780596528126/index.html
                col new for a30
                
                with t(X) as(
                select '1' from dual union
                select '1.1' from dual union
                select '1.1.1' from dual union
                select '1.1.2.a' from dual union
                select '1.1.2.b' from dual union
                select '1.2' from dual UNION
                Select '10.2.3' from dual UNION
                select '2.1.3' from dual)
                select X,
                RegExp_Replace(RegExp_Replace(X,'([^.]+)','     \1'),
                               ' +([^.]{5})','\1') as new
                  from t
                order by new;
                
                X        NEW
                -------  -----------------------
                1            1
                1.1          1.    1
                1.1.1        1.    1.    1
                1.1.2.a      1.    1.    2.    a
                1.1.2.b      1.    1.    2.    b
                1.2          1.    2
                2.1.3        2.    1.    3
                10.2.3      10.    2.    3
                • 6. Re: Order by Bulleted Numbers
                  Frank Kulash
                  Hi,

                  You can split each x into its ,-delimited parts, pad the numbers to a fixed length, then re-combine the parts into a column suitable for sorting.
                  it's tedious.
                  with t as(
                  select '1' x from dual
                  union
                  select '1.1' from dual
                  union
                  select '1.1.1' from dual
                  union
                  select '1.1.2.a' from dual
                  union
                  select '1.1.2.b' from dual
                  union
                  select '1.2' from dual
                  UNION
                  Select '10.2.3' from dual
                  UNION
                  select '2.1.3' from dual
                  )
                  ,     got_section_cnt     AS
                  (
                       SELECT     x
                       ,     1 + LENGTH (x)
                              - LENGTH (REPLACE (x, '.'))     AS section_cnt
                       FROM     t
                  )
                  ,     cntr     AS
                  (
                       SELECT     LEVEL     AS n
                       FROM     (
                                 SELECT  MAX (section_cnt)     AS max_section_cnt
                                 FROM     got_section_cnt
                            )
                       CONNECT BY     LEVEL     <= max_section_cnt
                  )
                  ,     got_section_txt     AS
                  (
                       SELECT     s.x
                       ,     c.n
                       ,     REGEXP_SUBSTR ( s.x
                                       , '[^.]+'
                                       , 1
                                       , c.n
                                       )          AS section_txt
                       FROM     got_section_cnt     s
                       JOIN     cntr          c     ON     c.n     <= s.section_cnt
                  )
                  ,     got_sort_txt     AS
                  (
                       SELECT      x
                       ,     SYS_CONNECT_BY_PATH ( CASE
                                             WHEN  REGEXP_LIKE ( section_txt
                                                         , '^[0-9]+$'
                                                         )
                                             THEN  TO_CHAR ( section_txt
                                                        , '0000000000'
                                                        )
                                             ELSE  section_txt
                                            END
                                             , '.'
                                          )          AS sort_txt
                       FROM       got_section_txt
                       WHERE       CONNECT_BY_ISLEAF     = 1
                       START WITH     n     = 1
                       CONNECT BY     n     = PRIOR n + 1
                            AND     x     = PRIOR x
                  )
                  SELECT       x
                  FROM       got_sort_txt
                  ORDER BY  sort_txt
                  ;
                  This does not assume that you know how many '.'s can be in x.
                  This does assume that you know an upper bound to the number of digits in the numeric part. I assumed there would never be more than 10 digits, so I used 10 '0's in
                  TO_CHAR (section_txt, '0000000000')
                  • 7. Re: Order by Bulleted Numbers
                    Nicosa-Oracle
                    Hi Aketi Jyuuzou,

                    Neat solution !
                    I fail to understand why the regexp_replace is used twice. Can you shed some light ?
                    • 8. Re: Order by Bulleted Numbers
                      Frank Kulash
                      Hi,

                      As Nicosa said, Very nice!

                      As posted, it sorts by length first, then by string value. That's fine for numbers (assuming they don't have leading 0's) but not for strings: it would sort 'b' before 'ax', for example, for the same reason that it would sort '2' before '19'.
                      We can fix that with another nested REGEXP_REPLACE.
                      SELECT      x
                      ,      REGEXP_REPLACE ( REGEXP_REPLACE ( REGEXP_REPLACE ( x
                                                                 , '([^.]+)'
                                                           , '\1     '
                                                           )
                                                , '([^.]{5}) +'
                                                , '\1'
                                                )
                                     , '([0-9]+)( +)'
                                     , '\2\1'
                                     )          AS new
                      FROM      t
                      ORDER BY  new
                      ;
                      As Nicosa asked: Why do we need nested REGEXP_REPLACE functions?
                      The innermost one adds 5 spaces to the end of each .-delimited item in the list. (This solution assumes that no item in the original list will be longer than 5 characters. We can make that limit 10, or 100, or anything else, as long as the padded values still fit in a 4000-character VARCHAR2.)
                      The middle REGEXP_REPLACE truncates each item so it is exactly 5 characters. This may include as many as 4 or as few as 0 of the spaces added in the first step.
                      The outer REGEXP_REPLACE reverse the order of items that contain nothing but digits and spaces, so that the spaces (however many there may be) come before the digits instead of after them. This still won't sort numbers with leading 0's properly, but that may not be a big problem.

                      If we were doing this in PL/SQL, we might use functions like RPAD, but there's no way to incorporate RPAD into REGEXP_REPLACE. In REGEXP_REPLACE, we can say things like 'Replace any string that does not contain a '.' with itself (\1), followed by 5 spaces", but we cannot say things like "Replace any string that does not contain a ',' with RPAD (\1, 5)". So we're forced to use nested REGEXP_REPLACE functions instead.
                      • 9. Re: Order by Bulleted Numbers
                        Aketi Jyuuzou
                        Nicosa wrote:
                        Neat solution !
                        I fail to understand why the regexp_replace is used twice. Can you shed some light ?
                        I am not good at English.
                        I live in Japan.
                        But I try to explain.
                        RegExp_Replace(RegExp_Replace(X,'([^.]+)','     \1'),
                                       ' +([^.]{5})','\1')
                        First RegExp_Replace makes add 5 spaces each string which are separated "."
                        Second RegExp_Replace cuts Left string greater than 5 letters which are separated "."

                        This regex book is very helpful to understand my solution.
                        http://oreilly.com/catalog/9780596528126/index.html

                        There is a good news :-)
                        native speaker of English Frank Kulash has explained my solution.
                        • 10. Re: Order by Bulleted Numbers
                          Nicosa-Oracle
                          Hi,

                          thanks to you (and also Franck Kulash) for your explanations.

                          I'll take a look to the book you are talking about.