This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Nov 20, 2012 1:48 AM by extreme RSS

Query to Reverse of a word

extreme Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Query to Reverse of a word
    KeithJamieson Expert
    Currently Being Moderated
    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
    extreme Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

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