1 2 Previous Next 26 Replies Latest reply: Jan 17, 2013 11:01 AM by BrendanP Go to original post RSS
      • 15. Re: Sort tokens in a string
        947561
        Hi, My database is 11.1
        select * from v$version;
        
        Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
        PL/SQL Release 11.1.0.6.0 - Production
        "CORE     11.1.0.6.0     Production"
        TNS for Linux: Version 11.1.0.6.0 - Production
        NLSRTL Version 11.1.0.6.0 - Production
        I want to perform this operation first for all the existing 25k rows present in the table and then on a routine basis as new rows are added to the table. I am not sure whether to use procedures or simply SQL for this task. Moreover, the solutions suggested are new to me. So I will go through all the solutions one by one and reply once I successfully accomplish this.

        Thanks to all for your valuable inputs.
        • 16. Re: Sort tokens in a string
          Billy~Verreynne
          APC wrote:
          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
          Yeah.. the danger of leaving Odie alone with sqlplus and database. He will find all kinds of neat and interesting uses for it. ;-)
          • 17. Re: Sort tokens in a string
            Billy~Verreynne
            944558 wrote:

            I want to perform this operation first for all the existing 25k rows present in the table and then on a routine basis as new rows are added to the table. I am not sure whether to use procedures or simply SQL for this task.
            There are 2 issues actually.

            The fix for existing data. The fix for new data.

            The existing data fix - no need for paying specific attention to finding the fastest way to do it. 25K rows are tiny. Even if the method used to fix the data is not highly optimised and tuned, the fix will be fast and quick on that few rows.

            The fix for new data is more complex. This fix should run whenever a row is inserted or updated and ensure that the tokens are correctly sorted in the string. This means implementing a trigger. Or if this is a well designed system with a PL/SQL abstraction interface for applications, updating the PL/SQL code that touches the relevant table for updates and inserts.

            It also raises the issue of exactly where you are fixing the data. With a trigger or a PL/SQL interface, that string will reside in a PL/SQL variable. Which means using a SQL-based solution for PL/SQL string is perhaps not the best idea - as it means cursors are needed to be parsed, bind variables send to the SQL engine, cursors executed and all that.

            Context switching from PL/SQL code to the SQL engine needs to be warranted. One should not use the SQL engine for doing what the PL/SQL language is perfectly capable of doing.

            So you can tokenise that PL/SQL variable containing the string using PL/SQL code only - example in {message:id=4420148}.

            Likewise, you can sort the tokens using PL/SQL - example in {message:id=10199271}.

            Which means in the trigger or stored procs dealing with the inserted/updated string as a PL/SQL variable, there is no need to ship that to SQL engine for processing.

            Of course, it makes sense to do a benchmark of this. From PL/SQL call SQL to tokenise and sort a PL/SQL string. From PL/SQL stay inside PL/SQL to tokenise and sort a PL/SQL string. And determine whether staying inside PL/SQL is the optimal approach.
            • 18. Re: Sort tokens in a string
              947561
              Thanks everyone,

              I did it using Karthick's solution:
              MERGE INTO employee f
              USING (  with emp
              as
              (
              select DISTINCT EMP_NAME name from employee
              )
              select DISTINCT name, ltrim(sys_connect_by_path(name_token, ' '), ' ') sorted_name
                from (
                        select name, rno, row_number() over(partition by rno order by name_token) token_no, name_token
                          from (
                                  select name, rno, regexp_substr(name, '[^ ]+', 1, no) name_token
                                    from (
                                            select rownum rno, name
                                              from emp
                                         )
                                   cross       
                                    join (
                                            select level no
                                              from dual
                                           connect 
                                                by level <= (
                                                              select max(length(name) - length(replace(name, ' ')) + 1)
                                                                from emp
                                                            )
                                         )
                                   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 ) a ON (f.EMP_NAME = a.name) WHEN MATCHED THEN UPDATE SET f.SORTED_EMP_NAME = a.sorted_name;
              SELECT EMP_NAME, SORTED_EMP_NAME FROM EMPLOYEE;
              
              EMP_NAME                 SORTED_EMP_NAME
              ------------------------------------------------------------------------------------------------------------------
              JANA DAMINI NAGAR     DAMINI JANA NAGAR
              NEEL KISHAN CHOUDHARY     CHOUDHARY KISHAN NEEL
              JYOTI AGRAWAL LAL     AGRAWAL JYOTI LAL
              Now, I will work on triggers for the second part.
              • 19. Re: Sort tokens in a string
                886282
                In Such a usage of table , I can say the table structure is wrong , better you should have firstname,lastname,middlename column, that is what most of the applications doing now.. Or if you want you can create a view of the same by using the existing table with a select having the substrings and all...
                • 20. Re: Sort tokens in a string
                  BrendanP
                  Yes, I had triggers in mind for the ongoing case. You don't need SQL within the trigger, my function can be used as follows (having put it into a database package). Trigger creation:
                  SQL> CREATE OR REPLACE TRIGGER Order_Tokens_TRG_BI BEFORE
                    2    INSERT ON temp_values FOR EACH ROW
                    3  BEGIN
                    4
                    5    :NEW.str := Strings.Order_Tokens (:NEW.str);
                    6
                    7  END;
                    8  /
                  
                  Trigger created.
                  Insert example:
                  SQL> INSERT INTO temp_values (str) VALUES ('MANOJ KUMAR AGRAWAL')
                    2  /
                  
                  1 row created.
                  
                  SQL> COLUMN str FORMAT A30
                  SQL> SELECT * FROM temp_values
                    2  /
                  
                  STR
                  ------------------------------
                  AGRAWAL KUMAR MANOJ
                  • 21. Re: Sort tokens in a string
                    APC
                    883279 wrote:
                    In Such a usage of table , I can say the table structure is wrong , better you should have firstname,lastname,middlename column
                    As I have already observed without context we cannot make observations about the quality of the data model. I can think of several valid reasons why the names might need to be concatenated into a single column.

                    Plus your data model is still flawed. Some people have more than one middle name. Also the meaning of "first name" and "last name" varies betwen cultures. Sometimes the famiiy name comes first, other times it's the given name. Those are important distinctions: how many times has some person in a call centre though they were being friendly by calling me "Clarke"?

                    So unless you have have a structured data model which can handle the naming conventions of both English and Nigerian aristocrats, frankly you might be better off with a concatenated string.

                    Cheers, APC
                    • 22. Re: Sort tokens in a string
                      BluShadow
                      APC wrote:
                      883279 wrote:
                      In Such a usage of table , I can say the table structure is wrong , better you should have firstname,lastname,middlename column
                      As I have already observed without context we cannot make observations about the quality of the data model. I can think of several valid reasons why the names might need to be concatenated into a single column.

                      Plus your data model is still flawed. Some people have more than one middle name. Also the meaning of "first name" and "last name" varies betwen cultures. Sometimes the famiiy name comes first, other times it's the given name. Those are important distinctions: how many times has some person in a call centre though they were being friendly by calling me "Clarke"?

                      So unless you have have a structured data model which can handle the naming conventions of both English and Nigerian aristocrats, frankly you might be better off with a concatenated string.

                      Cheers, APC
                      I had assumed the data was just some test data from the OP. Certainly I can't think of any valid reason for taking people's individual names and putting them in alphabetical order.
                      That's why I assumed it was just a request to re-order tokenised data that is essentially storing multiple values in a single value column. Agreed, it could be a legacy issue, but if not then now is the time to re-think the design.

                      I also agree with you that 883279's recommendation is wrong. Aside from what you pointed out, creating idividual columns for surname, middle name(s) and forename doesn't resolve the 'ordering tokens alphabetically' that the OP was asking about.
                      • 23. Re: Sort tokens in a string
                        BrendanP
                        Bit easier in Perl :)
                        $ perl token.pl "MANOJ KUMAR AGRAWAL"
                        AGRAWAL KUMAR MANOJ
                        $ cat token.pl
                        print join (' ', sort split (' ', $ARGV[0])), "\n";
                        • 24. Re: Sort tokens in a string
                          Stew Ashton
                          BrendanP wrote:
                          Bit easier in Perl :)
                          $ perl token.pl "MANOJ KUMAR AGRAWAL"
                          AGRAWAL KUMAR MANOJ
                          $ cat token.pl
                          print join (' ', sort split (' ', $ARGV[0])), "\n";
                          Well, Marc's solution isn't too much longer:
                          define X = MANOJ KUMAR AGRAWAL
                          SELECT * from xmltable('string-join(for $i in ora:tokenize($X," ") order by $i return $i," ")' passing '&X' as x);
                          
                          COLUMN_VALUE
                          -------------------
                          AGRAWAL KUMAR MANOJ
                          • 25. Re: Sort tokens in a string
                            BluShadow
                            BrendanP wrote:
                            Bit easier in Perl :)
                            $ perl token.pl "MANOJ KUMAR AGRAWAL"
                            AGRAWAL KUMAR MANOJ
                            $ cat token.pl
                            print join (' ', sort split (' ', $ARGV[0])), "\n";
                            It's also easier on paper... just tear up the paper and re-arrange by hand.
                            But as this is an Oracle SQL and PL/SQL forum... neither solution is suited.
                            • 26. Re: Sort tokens in a string
                              BrendanP
                              It was a comment on PL/SQL, not a proposed solution!
                              1 2 Previous Next