11 Replies Latest reply on Feb 7, 2013 10:38 AM by Purvesh K

    Sorting of character

    sm*290887*m7
      Hi all,

      I got the following data for which I will like to sort in ascending order.

      B51-01-008/04022013/CRE1005/1
      B51-01-008/04022013/CRE1005/2
      B51-01-008/15-1-13/SSA6280/1
      B51-01-008/31012013/CRE1005/1
      B51-01-008/31012013/CRE1005/10
      B51-01-008/31012013/CRE1005/2

      and expected result will be

      B51-01-008/15-1-13/SSA6280/1
      B51-01-008/31012013/CRE1005/1
      B51-01-008/31012013/CRE1005/2
      B51-01-008/31012013/CRE1005/10
      B51-01-008/04022013/CRE1005/1
      B51-01-008/04022013/CRE1005/2

      I will like to sort it based on the order on second segment for which is date, the earlier appear first then only the rest.

      How to do this sorting ?

      Thanks.
      Lim
        • 1. Re: Sorting of character
          Purvesh K
          This way:
          alter session set nls_date_format = 'DD-Mon-YYYY';
          
          with data as
          (
            select 'B51-01-008/04022013/CRE1005/1' col from dual union all
            select 'B51-01-008/04022013/CRE1005/2' col from dual union all
            select 'B51-01-008/15-1-13/SSA6280/1' col from dual union all
            select 'B51-01-008/31012013/CRE1005/1' col from dual union all
            select 'B51-01-008/31012013/CRE1005/10' col from dual union all
            select 'B51-01-008/31012013/CRE1005/2' col from dual
          )
          select 
                 col
            from (
                  select col, substr(col, instr(col, '/') + 1,  instr(col, '/', 1, 2) - instr(col, '/', 1, 1) - 1) dt
                    from data
                 )
           order by case 
                  when instr(dt, '-') > 0
                    then to_date(dt, 'DD-MM-YY')
                  else
                    to_date(dt, 'DDMMYYYY')
                 end;
          
          session set altered.
          COL                            
          ------------------------------ 
          B51-01-008/15-1-13/SSA6280/1   
          B51-01-008/31012013/CRE1005/2  
          B51-01-008/31012013/CRE1005/1  
          B51-01-008/31012013/CRE1005/10 
          B51-01-008/04022013/CRE1005/2  
          B51-01-008/04022013/CRE1005/1  
          
           6 rows selected 
          • 2. Re: Sorting of character
            sm*290887*m7
            Hi Purvesh,

            The last number still not in order

            Your result show

            B51-01-008/31012013/CRE1005/2
            B51-01-008/31012013/CRE1005/1

            it should be
            B51-01-008/31012013/CRE1005/1
            B51-01-008/31012013/CRE1005/2

            because I am have other code which having /3, /4, /8, /10 on the fourth segment, there got to be in ascending order as well. Hope you can understand my point here.

            Thanks.

            Lim
            • 3. Re: Sorting of character
              Purvesh K
              I got it. Sorry, I did not read your question quite carefully.
              with data as
              (
                select 'B51-01-008/04022013/CRE1005/1' col from dual union all
                select 'B51-01-008/04022013/CRE1005/2' col from dual union all
                select 'B51-01-008/15-1-13/SSA6280/1' col from dual union all
                select 'B51-01-008/31012013/CRE1005/1' col from dual union all
                select 'B51-01-008/31012013/CRE1005/10' col from dual union all
                select 'B51-01-008/31012013/CRE1005/2' col from dual
              )
              select 
                     col
                from (
                      select col, substr(col, instr(col, '/') + 1,  instr(col, '/', 1, 2) - instr(col, '/', 1, 1) - 1) dt,
                             to_number(substr(col, instr(col, '/', -1) + 1)) num
                        from data
                     )
               order by case 
                      when instr(dt, '-') > 0
                        then to_date(dt, 'DD-MM-YY')
                      else
                        to_date(dt, 'DDMMYYYY')
                     end, num;
              
              
              COL                            
              ------------------------------ 
              B51-01-008/15-1-13/SSA6280/1   
              B51-01-008/31012013/CRE1005/1  
              B51-01-008/31012013/CRE1005/2  
              B51-01-008/31012013/CRE1005/10 
              B51-01-008/04022013/CRE1005/1  
              B51-01-008/04022013/CRE1005/2  
              
               6 rows selected 
              • 4. Re: Sorting of character
                Rahul_India
                      sm****     
                      
                      
                Handle:      sm****  
                Status Level:      Newbie
                Registered:      May 21, 2002
                Total Posts:      280
                Total Questions:      38 (29 unresolved)
                Why dont you mark questions as answered?

                Edited by: Rahul India on Feb 7, 2013 2:03 PM
                • 5. Re: Sorting of character
                  Paul  Horth
                  The others have given good answers but I would question the design.

                  Is this data in a column in a table? If so, it appears to have a lot of embedded information
                  in it that may be better put in separate columns. The date part, in particular, in a date column:
                  then you won't have so many problems in the future.

                  Is there any reason all the information is concatenated together?
                  • 6. Re: Sorting of character
                    Rahul_India
                    Simple Solution
                    with data as
                    (
                      select 'B51-01-008/04022013/CRE1005/1' col from dual union all
                      select 'B51-01-008/04022013/CRE1005/2' col from dual union all
                      select 'B51-01-008/15-1-13/SSA6280/1' col from dual union all
                      select 'B51-01-008/31012013/CRE1005/1' col from dual union all
                      select 'B51-01-008/31012013/CRE1005/10' col from dual union all
                      select 'B51-01-008/31012013/CRE1005/2' col from dual
                    )
                    
                    select col
                    from data
                    order by to_date(substr(col,instr(col,'/')+1,instr(col,'/',1,2)-instr(col,'/',1,1)-1 ),'dd-mm-yy'),
                    to_number(substr(col,instr(col,'/',1,3)+1))
                    Result of the query
                    B51-01-008/15-1-13/SSA6280/1
                    B51-01-008/31012013/CRE1005/1
                    B51-01-008/31012013/CRE1005/2
                    B51-01-008/31012013/CRE1005/10
                    B51-01-008/04022013/CRE1005/1
                    B51-01-008/04022013/CRE1005/2
                    Can agree more with Paul.They very basic principle of table design -1st Normal form being disobeyed .
                    Each column should contain atomic values only

                    Edited by: Rahul India on Feb 7, 2013 1:57 PM
                    • 7. Re: Sorting of character
                      sm*290887*m7
                      Hi Paul,

                      This is the code generated from our batch inventory module. Each segment represent difference meaning. I cannot change the format but try to find ways to solve my reporting issue.

                      Thanks.
                      Lim
                      • 8. Re: Sorting of character
                        sm*290887*m7
                        Hi Rahul/ Purvesh,

                        Thanks. You guys are really great. It make my problem solved.

                        Regards,
                        Lim
                        • 9. Re: Sorting of character
                          Rahul_India
                          sm**** wrote:
                          Hi Rahul/ Purvesh,

                          Thanks. You guys are really great. It make my problem solved.

                          Regards,
                          Lim
                          Your Welcome.Kindly mark your this and other questions as answered.
                          • 10. Re: Sorting of character
                            Paul  Horth
                            sm**** wrote:
                            Hi Paul,

                            This is the code generated from our batch inventory module. Each segment represent difference meaning. I cannot change the format but try to find ways to solve my reporting issue.

                            Thanks.
                            Lim
                            You might not be able to, but someone should have the authority to make changes at your organisation.

                            The DB designer should take responsibility and design this properly.
                            • 11. Re: Sorting of character
                              Purvesh K
                              sm**** wrote:
                              Hi Paul,

                              This is the code generated from our batch inventory module. Each segment represent difference meaning. I cannot change the format but try to find ways to solve my reporting issue.

                              Thanks.
                              Lim
                              You have an Alternative of Using Views.

                              Supposing you receive some data from another Table:
                              create or replace view vw_data as
                              select substr(col, 1, instr(col, '/')) Person_ID, substr(col, instr(col, '/', 1, 2) + 1, instr(col, instr(col, '/', 1, 1) -1 ) some_other_column
                                from your_table_source;
                              By this way, you can then use the View to project the Information without requiring you to manually apply these operations on data everytime you query the tables.

                              This won't completely fix the Table issues you have but will atleast project that you have lesser issues.