This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Jan 17, 2013 9:01 AM by BrendanP RSS

Sort tokens in a string

947561 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points