1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to reverse a string in a query itself without using reverse function?

Discussion in 'SQL PL/SQL' started by Vicky, Jul 26, 2016.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    However, I've tried using a query below:

    Code (SQL):
    SELECT substr('ABCDEF',-level) reverse_string FROM dual CONNECT BY level<=LENGTH('ABCDEF');
    Actual Output:
    ------------------
    REVERS
    ------
    F
    EF
    DEF
    CDEF
    BCDEF
    ABCDEF


    Expected output:
    ---------------------
    REVERS
    ------------
    FEDCBA
     
    Last edited: Jul 26, 2016
  2. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    I got somethng like this;

    Code (SQL):
    SELECT a||b||c||d||e AS rev FROM(
    SELECT MAX(decode(qa,1,letter)) a,
    MAX(decode(qa,2,letter)) b,
    MAX(decode(qa,3,letter)) c,
    MAX(decode(qa,4,letter)) d,
    MAX(decode(qa,5,letter)) e
    FROM (
    SELECT level qa, SUBSTR ('hello', LEVEL*-1,1) letter
    FROM   dual
    CONNECT BY LEVEL <= LENGTH('hello')))

    REV
    -----
    olleh


    Is der any better/dynamic way to achieve this?
     
  3. Siddhartha

    Siddhartha Active Member

    Messages:
    16
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    Bangalore
    select replace(wm_concat(sid),',','') abcd from
    (
    select level,substr('hello',level,1) sid from dual
    connect by level<= length('hello')
    order by level desc
    )
     
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi Sid,

    Thanks for your Reply.

    I've tried WM_CONCAT,

    As it's an undocumented feature, it's not been supported by my Oracle Version : Oracle Database 10g Express Edition Release 10.2.0.1.0
     
  5. Siddhartha

    Siddhartha Active Member

    Messages:
    16
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    Bangalore
    You're Welcome..Wm_concat is available in Oracle 11g release 2...
     
  6. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes. Could U tel me any Equivalent for WM_CONCAT on Oracle 10g.?
     
  7. Siddhartha

    Siddhartha Active Member

    Messages:
    16
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    Bangalore
    I think there is nothing...Even Listagg() function also available from oracle 11g...
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,348
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This should work in later releases of Oracle:

    Code (SQL):
    SET verify off
    SELECT REPLACE(listagg(sid) WITHIN GROUP (ORDER BY lvl DESC), ',','') reverse_string FROM
    (
    SELECT level lvl,substr('&&1',level,1) sid FROM dual
    CONNECT BY level<= LENGTH('&&1')
    ORDER BY level DESC
    )
    /

    undefine 1
    SET verify ON

     
     
    Vicky likes this.
  9. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    So, It is not possible to achieve this in Query in lower versions, despite using Plsql right..
     
  10. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Any Idea about splitting the input into parts using query?..

    For eg:
    '123456' into
    12
    34
    56
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,348
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The wm_concat version will work in lower versions of Oracle; also PL/SQL blocks should work in any version of Oracle, however you will need to write a parser to get the characters in the string. I've blogged a method of doing that in PL/SQL here:

    https://dfitzjarrell.wordpress.com/2008/09/26/how-dynamic/

    I used it to parse a dynamic list of values in a string; you should be able to modify the code to do what you want it to do.