1 2 Previous Next 26 Replies Latest reply: Jan 17, 2013 11:01 AM by BrendanP RSS

    Sort tokens in a string

    947561
      Hello,

      I have a table
      SQL> SELECT name FROM employee
      
      NAME
      ----------------------------------------------------------------------------------------------------
      KUMAR AJAY
      MANOJ KUMAR AGRAWAL
      KUSH KRANTI AMOL 
      SHETTY PRIYA RAVI
      Please let me know how can I get the individual names rearranged in an alphabetical order like:
      SQL> SELECT sorted_name FROM employee
      
      SORTED_NAME
      ----------------------------------------------------------------------------------------------------
      AJAY KUMAR
      AGRAWAL KUMAR MANOJ
      AMOL KRANTI KUSH
      PRIYA RAVI SHETTY
      Edited by: 944558 on 16 Jan, 2013 3:35 AM
        • 1. Re: Sort tokens in a string
          AlbertoFaenza
          944558 wrote:
          Hello,

          I have a table
          SQL> SELECT name FROM employee
          
          NAME
          ----------------------------------------------------------------------------------------------------
          KUMAR AJAY
          MANOJ KUMAR AGRAWAL
          KUSH KRANTI AMOL 
          SHETTY PRIYA RAVI
          Please let me know how can I get the individual names rearranged in an alphabetical order like:
          SQL> SELECT sorted_name FROM employee
          
          SORTED_NAME
          ----------------------------------------------------------------------------------------------------
          AJAY KUMAR
          AGRAWAL KUMAR MANOJ
          AMOL KRANTI KUSH
          PRIYA RAVI SHETTY
          Edited by: 944558 on 16 Jan, 2013 3:35 AM
          Hi,

          how could we know the criteria to understand which is the first name and which is the last name.

          In row 2 AGRAWAL is the last word
          In row 4 PRIYA is the second word.

          How do you decide if taking the second word or the last word?

          Regards.
          Al
          • 2. Re: Sort tokens in a string
            APC
            This actually is a tricky problem in SQL. Actually two tricky problems:

            1. Split the string into tokens
            2. Re-aggregate those tokens in sorted order.

            Neither of these problems are insoluble but solutions depend on
            * which version of the database you're using,
            * how much freedom you have to build additional objects such as types and functions.

            Cheers, APC
            • 3. Re: Sort tokens in a string
              Karthick_Arp
              May be
              with employee
              as
              (
              select 'KUMAR AJAY' name from dual
              union all
              select 'MANOJ KUMAR AGRAWAL' name from dual
              union all
              select 'KUSH KRANTI AMOL ' name from dual
              union all
              select 'SHETTY PRIYA RAVI' name from dual
              )
              select ltrim(sys_connect_by_path(name_token, ' '), ' ') new_name
                from (
                        select rno, row_number() over(partition by rno order by name_token) token_no, name_token
                          from (
                                  select rno, regexp_substr(name, '[^ ]+', 1, no) name_token
                                    from (
                                            select rownum rno, name
                                              from employee
                                         )
                                   cross       
                                    join (
                                            select level no
                                              from dual
                                           connect 
                                                by level <= (
                                                              select max(length(name) - length(replace(name, ' ')) + 1)
                                                                from employee
                                                            )
                                         )
                                   where regexp_substr(name, '[^ ]+', 1, no) is not null  
                               )    
                     )
               where connect_by_isleaf = 1       
               start
               with token_no = 1
              connect
                 by rno = prior rno
                and token_no = prior token_no + 1;
              
              NAME                NEW_NAME           
              ------------------- --------------------
              KUMAR AJAY          AJAY KUMAR           
              MANOJ KUMAR AGRAWAL AGRAWAL KUMAR MANOJ  
              KUSH KRANTI AMOL    AMOL KRANTI KUSH     
              SHETTY PRIYA RAVI   PRIYA RAVI SHETTY    
              • 4. Re: Sort tokens in a string
                Purvesh K
                This way
                with data as
                (
                  select 'KUMAR AJAY' col from dual union all
                  select 'MANOJ KUMAR AGRAWAL' col from dual union all
                  select 'KUSH KRANTI AMOL' col from dual union all
                  select 'SHETTY PRIYA RAVI' col from dual
                ),
                mod_data as
                (
                  select col || ' ' col from data
                ),
                split_data as
                (
                  select regexp_substr(col, '[^ ]+', 1, level) col1,
                         dense_rank() over ( order by col) rn
                    from mod_data
                  connect by level <= regexp_count(col, ' ')
                         and prior col = col
                         and prior sys_guid() is not null
                )
                select listagg(col1, ' ') within group (order by col1) col
                  from split_data
                 group by rn;
                
                COL
                -------------------------------------
                AJAY KUMAR 
                AMOL KRANTI KUSH 
                AGRAWAL KUMAR MANOJ 
                PRIYA RAVI SHETTY 
                • 5. Re: Sort tokens in a string
                  Frank Kulash
                  Hi,

                  I think you should split the strings into multiple rows, with the primary key and one token on each row, then use string aggregation to re-combine them in order.

                  The following threads show various ways of splitting the original strings:
                  i don't won't to tokenize in plsql
                  A question on collections (or maybe pure SQL can do it??)
                  Re: Column To Row Conversion

                  For several string aggregation techniques, see
                  http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
                  If you're using Oracle 11.2, I suggest the LISTAGG function; in earlier versions, SYS_CONNECT_BY_PATH might be best.

                   

                  I hope this answers your question.
                  If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and the results you want from that data.
                  Explain, using specific examples, how you get those results from that data.
                  Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
                  See the forum FAQ {message:id=9360002}
                  • 6. Re: Sort tokens in a string
                    947561
                    Hello,

                    Thanks for your prompt response.

                    Database is 11g.
                    is there anyway to implement this using procedure?

                    There are around 25k records in this table which needs to be tokenized and re-arranged so I just posted some sample data.

                    Edited by: 944558 on 16 Jan, 2013 4:08 AM
                    • 7. Re: Sort tokens in a string
                      Frank Kulash
                      Hi,
                      944558 wrote:
                      Hello,

                      Thanks for your prompt response.

                      Database is 11g.
                      There is no version 11<b>f</b> or 11<b>h</b>, so it's kind of silly to say you have 11<b>g</b>, especially now that you know that the difference between 11.1 and 11.2 is significant in this problem.
                      is there anyway to implement this using procedure?
                      Sure, you can use any of the techniques above in a procedure. A function would be more useful than a procedure, but either way will be slower than doing the same thing in pure SQL.
                      There are around 25k records in this table which needs to be tokenized and re-arranged so I just posted some sample data.
                      The solutions already given work with any number of rows.
                      • 8. Re: Sort tokens in a string
                        BluShadow
                        944558 wrote:
                        Hello,

                        Thanks for your prompt response.

                        Database is 11g.
                        is there anyway to implement this using procedure?
                        Why? Do you want to do it more slowly? SQL will always be faster than PL/SQL.
                        There are around 25k records in this table which needs to be tokenized and re-arranged so I just posted some sample data.
                        That's fine, just apply what people have given you to your data... it may be slow... but that's what happens when you don't store your data in a proper structure.

                        Q: Why isn't your data stored in a structure that allows it to be ordered by SQL simply? If you're storing multiple values in a single value column, then you haven't normalised your database design correctly.
                        • 9. Re: Sort tokens in a string
                          chris227
                          with data as (
                          select 1 id, 'KUMAR AJAY' name from dual
                          union all
                          select 2, 'MANOJ KUMAR AGRAWAL' name from dual
                          union all
                          select 3, 'KUSH KRANTI AMOL' name from dual
                          union all
                          select 4, 'SHETTY PRIYA RAVI' name from dual
                          )
                          , r(id,str,name) as (
                          select
                           id
                          ,substr(name,1,decode(instr(name,' '),0,length(name),instr(name,' ')-1))
                          ,substr(name,decode(instr(name,' '),0,1,instr(name,' ')+1))
                          from data
                          union all
                          select
                           id
                          ,substr(name,1,decode(instr(name,' '),0,length(name),instr(name,' ')-1))
                          ,substr(name,decode(instr(name,' '),0,1,instr(name,' ')+1))
                          from r
                          )
                          cycle name set is_cycle to 'Y' default 'N'
                          
                          select 
                          listagg(str,' ') within group(order by str) str
                          from r
                          group by id
                          
                          STR 
                          AJAY KUMAR 
                          AGRAWAL KUMAR MANOJ 
                          AMOL KRANTI KUSH 
                          PRIYA RAVI SHETTY 
                          • 10. Re: Sort tokens in a string
                            odie_63
                            http://odieweblog.wordpress.com/2011/11/28/how-to-sort-delimited-values-in-a-string-using-xquery/
                            SQL> with employee (id, name) as (
                              2   select 1, 'KUMAR AJAY'          from dual union all
                              3   select 2, 'MANOJ KUMAR AGRAWAL' from dual union all
                              4   select 3, 'KUSH KRANTI AMOL'    from dual union all
                              5   select 4, 'SHETTY PRIYA RAVI'   from dual
                              6  )
                              7  SELECT t.id
                              8       , x.sorted_name
                              9  FROM employee t
                             10     , XMLTable('string-join(for $i in ora:tokenize($str, " ") order by $i return $i, " ")'
                             11         passing t.name as "str"
                             12         columns sorted_name varchar2(4000) path '.'
                             13       ) x
                             14  ;
                             
                                    ID SORTED_NAME
                            ---------- --------------------------------------------------------------------------------
                                     1 AJAY KUMAR
                                     2 AGRAWAL KUMAR MANOJ
                                     3 AMOL KRANTI KUSH
                                     4 PRIYA RAVI SHETTY
                             
                            • 11. Re: Sort tokens in a string
                              APC
                              BluShadow wrote:
                              Q: Why isn't your data stored in a structure that allows it to be ordered by SQL simply? If you're storing multiple values in a single value column, then you haven't normalised your database design correctly.
                              Like a lot of data modelling questions, the answer is probably "legacy" or "interoperability". The data may well come from some source beyond the OP's control.

                              Cheers, APC
                              • 12. Re: Sort tokens in a string
                                BrendanP
                                Quick PL/SQL function demo. If it were a database function, I suppose you could do
                                 UPDATE emp SET name = Order_Tokens (name)
                                although I didn't test that...
                                SQL> DECLARE
                                  2  
                                  3  l_test_str      VARCHAR2(4000) := 'MANOJ KUMAR AGRAWAL';
                                  4  TYPE hash_type  IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(100);
                                  5  
                                  6  FUNCTION Order_Tokens (p_in_str VARCHAR2) RETURN VARCHAR2 IS
                                  7    l_str                 VARCHAR2(4000);
                                  8    l_nxt_sp              PLS_INTEGER;
                                  9    l_nxt_non_sp          PLS_INTEGER :=  RegExp_Instr (p_in_str, '\S', 1, 1);
                                 10    l_token               VARCHAR2(100);
                                 11    l_token_hash          hash_type;
                                 12  BEGIN
                                 13  
                                 14    WHILE l_nxt_non_sp IS NOT NULL LOOP
                                 15  
                                 16      l_nxt_sp := Instr (p_in_str, ' ', l_nxt_non_sp, 1);
                                 17      IF l_nxt_sp = 0 THEN
                                 18        l_token := Substr (p_in_str, l_nxt_non_sp);
                                 19      ELSE
                                 20        l_token := Substr (p_in_str, l_nxt_non_sp, l_nxt_sp - l_nxt_non_sp);
                                 21      END IF;
                                 22      l_token_hash (l_token) := 1;
                                 23      IF l_nxt_sp = 0 THEN
                                 24        EXIT;
                                 25      END IF;
                                 26      l_nxt_non_sp := RegExp_Instr (p_in_str, '\S', l_nxt_sp, 1);
                                 27  
                                 28    END LOOP;
                                 29  
                                 30    l_token := l_token_hash.FIRST;
                                 31    WHILE l_token IS NOT NULL LOOP
                                 32      l_str := l_str || ' ' || l_token;
                                 33      l_token := l_token_hash.NEXT (l_token);
                                 34    END LOOP;
                                 35    RETURN Substr (l_str, 2);
                                 36  
                                 37  END Order_Tokens;
                                 38  
                                 39  BEGIN
                                 40    DBMS_Output.Put_Line ('Input test string = ' || l_test_str);
                                 41    DBMS_Output.Put_Line ('Result string = ' || Order_Tokens (l_test_str));
                                 42  END;
                                 43  /
                                Input test string = MANOJ KUMAR AGRAWAL
                                Result string = AGRAWAL KUMAR MANOJ
                                
                                PL/SQL procedure successfully completed.
                                
                                SQL> 
                                • 13. Re: Sort tokens in a string
                                  APC
                                  944558 wrote:
                                  is there anyway to implement this using procedure?
                                  There are some nifty solutions here. As you're on the latest version you can take any of them. I would suggest using the RegEx for the tokenizing part and LISTAGG() for the gluing back together.

                                  Any of these solutions can be converted to a function which takes in a string, tokenizes it and returns a string of sorted tokens. However, this will be slower than using the pure SQL solutions. So you need to know why you want to wrap it in PL/SQL. The best reason would be because you need to re-run this capability from time to time, especially if the need is to update a single row.

                                  Cheers, APC
                                  • 14. Re: Sort tokens in a string
                                    APC
                                    For the benefit of others, I would just like to point out that Odie's solution - which appears to be highly neat - only works for 11.2 or later (because that's when oracle added {noformat}ora:tokenize{noformat} to their XQuery armoury).

                                    Cheers, APC
                                    1 2 Previous Next