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 replace characters other than specified without using regex in oracle sql?

Discussion in 'SQL PL/SQL' started by Vicky, Apr 11, 2016.

  1. Vicky

    Vicky Forum Advisor

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

    How to replace characters other than specified without using Regular Expression in oracle sql?

    For Example:

    Code (SQL):

    SELECT REPLACE ('rhythm','^h') FROM dual;

    Expected RESULT:

    hh
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    For Example:

    1st:

    Code (SQL):
    SELECT REPLACE(translate(REPLACE ('&your_str','&your_symbol','@'),'!'||'&your_str','!'),'@','&your_symbol')  str
    FROM dual;

    SQL>
    STR
    --------------------------------------------------------------------------------
    hh
     
    2nd(for testing):
    Code (SQL):

    SELECT listagg(ch,NULL) WITHIN GROUP (ORDER BY  l)  str
    FROM (
    SELECT
      substr('&your_str',level,1) ch,
      level l
    FROM dual
    WHERE
      substr('&&your_str',level,1) = '&your_symbol'
    CONNECT BY substr('&&your_str',level,1) IS NOT NULL
    );


    SQL>
    STR
    --------------------------------------------------------------------------------
    hh
     
     
    Last edited: Apr 11, 2016
    Vicky likes this.