1 2 Previous Next 29 Replies Latest reply: Nov 20, 2012 3:48 AM by Kalpataru RSS

    Query to Reverse of a word

    Kalpataru
      Hi all,
      i want to reverse the words any word or sentences
      for example
      string require
      --------------- ----------
      MY NAME IS RAMA RAMA IS MY NAME
      HE IS GOOD GOOD IS HE

      and another query i want is that
      i have a lot of descriptions like
      FREE PRODUCT SIZE 10 M
      FREE PRODUCT SIZE 20 M
      FREE PRODUCT SIZE 30 M
      ABC 123 ORACLE 123
      ABC 123 XYZ 123
      ABC 123 KLM 123
      i don't know what will be the string will come but i want to cut the common/repeated strings
      like in above example
      i want only
      SIZE 10 M
      SIZE 20 M
      SIZE 30 M
      ORACLE 123
      XYZ 123
      KLM 123

      please replay.
        • 1. Re: Query to Reverse of a word
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Query to Reverse of a word
            Keith Jamieson
            to reverse a string , you can just use reverse
            select reverse('this is the string backwards')  from dual;
            which gives
            'sdrawkcab gnirts eht si siht'
            You have to identify some rules for the other criteria
            How do we know what you want, or what you don't want?
            • 3. Re: Query to Reverse of a word
              Kalpataru
              hi Keith Jamieson i have clearly mentioned there

              suppose i have the word my name is rama
              and the query will retreve rama is name my
              not the reverse of the letters i want the shifting of the words.
              like last word is first rama the the next word is then name then my
              last word comes first and so on not the character wise shifting
              i want word wise shifting.

              please reply.
              • 4. Re: Query to Reverse of a word
                ranit B
                extreme wrote:
                and another query i want is that
                i have a lot of descriptions like
                FREE PRODUCT SIZE 10 M
                FREE PRODUCT SIZE 20 M
                FREE PRODUCT SIZE 30 M
                ABC 123 ORACLE 123
                ABC 123 XYZ 123
                ABC 123 KLM 123
                i don't know what will be the string will come but i want to cut the common/repeated strings
                like in above example
                i want only
                SIZE 10 M
                SIZE 20 M
                SIZE 30 M
                ORACLE 123
                XYZ 123
                KLM 123
                Why didn't you cut the word 'SIZE'? that is also gettin repeated right?
                Why didn't you cut the 2 ^nd^ column of '123'?

                Please explain the logic properly dude...
                • 5. Re: Query to Reverse of a word
                  ranit B
                  extreme wrote:
                  Hi all,
                  i want to reverse the words any word or sentences
                  for example
                  string require
                  --------------- ----------
                  MY NAME IS RAMA RAMA IS MY NAME
                  HE IS GOOD GOOD IS HE
                  Try this...
                  WITH xx AS(
                    SELECT 'my name is rama' a1 from dual 
                  )
                  SELECT rtrim(xmlagg(xmlelement(e,x.res||' ')).extract('//text()'),' ') 
                    from( SELECT reverse(regexp_substr(xx1.a2,'[^ ]+',1,rownum)) res 
                            from(SELECT reverse(a1) a2 from xx) xx1
                  connect by level <= length(regexp_replace(xx1.a2,'[^ ]+'))+1) x;
                  gives
                  o/p = rama is name my
                  HTH
                  Ranit B.
                  • 6. Re: Query to Reverse of a word
                    Another_user
                    SELECT LISTAGG (val, ' ') WITHIN GROUP (ORDER BY r DESC)
                      FROM (    SELECT REGEXP_SUBSTR ('FREE PRODUCT SIZE 10 M',
                                                      '[^[:blank:]]+',
                                                      1,
                                                      LEVEL)
                                          val,
                                       ROWNUM r
                                  FROM DUAL
                            CONNECT BY REGEXP_SUBSTR ('FREE PRODUCT SIZE 10 M',
                                                      '[^[:blank:]]+',
                                                      1,
                                                      LEVEL)
                                          IS NOT NULL)
                    • 7. Re: Query to Reverse of a word
                      Solomon Yakobson
                      ranit B wrote:

                      Try this...
                      a) REVERSE is undocumented
                      b) REVERSE reverses bytes not characters, so your code might not work in multi-byte character set
                      c) your code will not work if table xx has multiple rows
                      d) there is no need for XML.
                      e) There is no need to group by (XMLAGG). All it needs is WHERE connect_by_isleaf = 1

                      For a single row table using undocumented REVERSE (having above notes in mind):
                      with t as (
                                 select 'MY NAME IS RAMA' s from dual
                                )
                      select  ltrim(sys_connect_by_path(reverse(regexp_substr(reverse(s),'\w+',1,level)),' ')) r
                        from  t
                        where connect_by_isleaf = 1
                        connect by regexp_substr(reverse(s), '\w+', 1, level) is not null
                      /
                      
                      R
                      -----------------
                      RAMA IS NAME MY
                      
                      SQL> 
                      Solution for multiple row table without using REVERSE:
                      with t as (
                                 select 'MY NAME IS RAMA' s from dual union all
                                 select 'HE IS GOOD' from dual
                                )
                      select  ltrim(sys_connect_by_path(regexp_substr(s,'\w+',1,regexp_count(s,'\w+') + 1 - level),' ')) r
                        from  t
                        where connect_by_isleaf = 1
                        connect by s = prior s
                              and prior dbms_random.random is not null
                              and regexp_substr(s,'\w+',1,level) is not null
                      /
                      
                      R
                      ----------------
                      GOOD IS HE
                      RAMA IS NAME MY
                      
                      SQL> 
                      SY.
                      • 8. Re: Query to Reverse of a word
                        ranit B
                        Solomon Yakobson wrote:
                        Solution for multiple row table without using REVERSE:
                        with t as (
                        select 'MY NAME IS RAMA' s from dual union all
                        select 'HE IS GOOD' from dual
                        )
                        select  ltrim(sys_connect_by_path(regexp_substr(s,'\w+',1,regexp_count(s,'\w+') + 1 - level),' ')) r
                        from  t
                        where connect_by_isleaf = 1
                        connect by s = prior s
                        and prior dbms_random.random is not null
                        and regexp_substr(s,'\w+',1,level) is not null
                        Can you please explain its working?
                        • 9. Re: Query to Reverse of a word
                          Solomon Yakobson
                          ranit B wrote:

                          Can you please explain its working?
                          Pretty much same way. REGEXP_COUNT gives us number of words and REGEXP_COUNT + 1 - LEVEL gives us words numbers in reverse. PRIOD DBMS_RANDOM.RANDOM IS NOT NULL produces adifferent random number on each level thus avoiding CYCLE.

                          SY.
                          • 10. Re: Query to Reverse of a word
                            jeneesh
                            MODEL
                            SQL> with t as
                              2  (select 1 id,'my name is rama' str from dual
                              3   union all
                              4   select 2,'  i   am    an     oracle      fan ' from dual
                              5   union all
                              6   select 3,'i am an oracle fan ' from dual
                              7   union all
                              8   select 4,' i am an oracle fan' from dual
                              9  )
                             10  select id,str,rev
                             11  from t
                             12  model
                             13   partition by(id)
                             14   dimension by(1 rn)
                             15   measures (ltrim(str) as str, cast(regexp_substr(str,'^ +') as varchar2(100)) as rev)
                             16   rules
                             17    iterate(10) until(iteration_number+1 = regexp_count(str[1],'[^ ]+') )
                             18     (
                             19       rev[1] = regexp_substr(str[1],'[^ ]+',1,iteration_number+1)||rev[1],
                             20       rev[1] = regexp_substr(str[1],' +',1,iteration_number+1)||rev[1]
                             21     );
                            
                                    ID STR                                 REV
                            ---------- ----------------------------------- ----------------------------------------
                                     1 my name is rama                     rama is name my
                                     2 i   am    an     oracle      fan     fan      oracle     an    am   i
                                     4 i am an oracle fan                  fan oracle an am i
                                     3 i am an oracle fan                   fan oracle an am i
                            Edited by: jeneesh on Nov 10, 2012 10:55 AM
                            • 11. Re: Query to Reverse of a word
                              odie_63
                              11.2 and up :
                              SQL> with t as (
                                2     select 'MY NAME IS RAMA' s from dual union all
                                3     select 'HE IS GOOD' from dual
                                4  )
                                5  select x.result
                                6  from t
                                7     , xmltable(
                                8         'string-join(reverse(ora:tokenize($str,"\s+"))," ")'
                                9         passing s as "str"
                               10         columns result varchar2(4000) path '.'
                               11       ) x
                               12  ;
                               
                              RESULT
                              --------------------------------------------------------------------------------
                              RAMA IS NAME MY
                              GOOD IS HE
                               
                              • 12. Re: Query to Reverse of a word
                                AlbertoFaenza
                                extreme wrote:
                                hi Keith Jamieson i have clearly mentioned there

                                suppose i have the word my name is rama
                                and the query will retreve rama is name my
                                not the reverse of the letters i want the shifting of the words.
                                like last word is first rama the the next word is then name then my
                                last word comes first and so on not the character wise shifting
                                i want word wise shifting.
                                You have not "clearly mentioned" as your first post was misleading:
                                extreme wrote:
                                Hi all,
                                i want to reverse the words any word or sentences
                                for example
                                string require
                                --------------- ----------
                                MY NAME IS RAMA RAMA IS MY NAME
                                RAMA IS MY NAME is not RAMA IS NAME MY!!

                                On top of that, <u><b>despite your 165 posts</b></u> it seems you haven't learnt how to use the {noformat}
                                {noformat} tag when posting some code or result. The result is that your message was not so clear to many of the readers here.
                                
                                Look at the difference: 
                                <b>Without {noformat}
                                {noformat} tag</b>

                                string require
                                --------------- ----------
                                MY NAME IS RAMA RAMA IS MY NAME


                                <b>With {noformat}
                                {noformat} tag</b>
                                string require
                                --------------- ----------
                                MY NAME IS RAMA RAMA IS MY NAME
                                Isn't it better?
                                You can always see how your message will appear looking at the preview tab.
                                
                                If I have to add something else, why should someone bother to answer you if you don't care marking as answered many questions that you have posted:
                                Handle:      extreme
                                Status Level:      Newbie
                                Registered:      Dec 20, 2009
                                Total Posts:      165
                                Total Questions:      60 (43 unresolved)
                                I'm sure that the 43 questions you have posted are not all unresolved. Like this one. 
                                
                                
                                please reply.
                                Please mark your questions as answered or post some additional information if you're not satisfied with the answers. Please read <a href="https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360002">How do I ask a question on the forums?</a> to understand how to post your questions. Regards. Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                                • 13. Re: Query to Reverse of a word
                                  Manik
                                  Which version of oracle are you using?
                                  WITH t AS (SELECT 'MY NAME IS RAMA' dc FROM DUAL),
                                       t1 AS
                                          (    SELECT REGEXP_SUBSTR (dc,
                                                                     '[^ ]+',
                                                                     1,
                                                                     ROWNUM)
                                                         dc,
                                                      LEVEL lvl
                                                 FROM t
                                           CONNECT BY ROWNUM <= REGEXP_COUNT (dc, ' ') + 1)
                                  SELECT listagg (dc, ' ') WITHIN GROUP (ORDER BY lvl DESC) finalstr
                                    FROM t1;
                                  
                                  output:
                                  ----------------
                                  RAMA IS NAME MY
                                  Cheers,
                                  Manik
                                  • 14. Re: Query to Reverse of a word
                                    ranit B
                                    Try this.... won't work for table with multiple rows.
                                    /* Formatted on 11/12/2012 10:25:56 AM (QP5 v5.163.1008.3004) */
                                    WITH t AS (
                                        SELECT 'MY NAME IS RAMA' dc FROM DUAL UNION ALL
                                        SELECT 'i am good' dc FROM DUAL), /* new row added */
                                    t1 AS (
                                        SELECT REGEXP_SUBSTR (dc,'[^ ]+',1,ROWNUM) dc, LEVEL lvl
                                                   FROM t
                                             CONNECT BY ROWNUM <= REGEXP_COUNT (dc, ' ')+1
                                    )
                                    SELECT listagg (dc, ' ') WITHIN GROUP (ORDER BY lvl DESC) finalstr
                                      FROM t1;
                                    1 2 Previous Next