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 only exact whole values using regexp_replace..?

Discussion in 'SQL PL/SQL' started by Vicky, Sep 23, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi frnds..

    I just want to replace only exact whole values ('a') in the input, but not the one in combination.,

    Code (SQL):
    SELECT regexp_replace('a,ca,va,ea','a','X') FROM dual;

    Current Output:

    REGEXP_REPLACE('A,CA,VA,EA','A','X')
    ------------------------------------
    X,cX,vX,eX

    Expected Output:

    REGEXP_REPLACE('A,CA,VA,EA','A','X')
    ------------------------------------
    X,ca,va,ea

    How to achieve this frnds/.?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Code (SQL):
    SELECT regexp_replace('a,ca,va,ea','^a','X') FROM dual;
    if the character (a) can is not only at the beginning of a line, then
    it is necessary to create correctly a template that correctly changeover happened
     
    Last edited: Sep 23, 2015
    Vicky likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    SQL> --
    SQL> -- The following starts with the first occurrence of
    SQL> -- the character to replace and continues on through the
    SQL> -- string replacing all matching characters
    SQL> --
    SQL> SELECT regexp_replace('a,ca,va,ea','a','X') FROM dual;

    REGEXP_REP
    ----------
    X,cX,vX,eX

    SQL>
    SQL> --
    SQL> -- To restrict this behavior it's necessary to
    SQL> -- explicitly start with the first occurrence and
    SQL> -- then stop once that replacement has been made
    SQL> --
    SQL> -- This is done by supplying two additional parameters,
    SQL> -- the starting occurrence and the ending occurrence,
    SQL> -- as shown below:
    SQL> --
    SQL> SELECT regexp_replace('a,ca,va,ea','a','X',1,1) FROM dual;

    REGEXP_REP
    ----------
    X,ca,va,ea

    SQL>
     
  4. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Thanks Sergey.. how to do if I wanna replace also ('va'&'ea')..

    Code (SQL):
    SELECT regexp_replace('a,ca,va,ea','(^a)(^va)(^ea)','X') FROM dual;
    Current Output:

    REGEXP_REPLACE('A,CA,VA,EA','A','X')
    ------------------------------------
    a,ca,va,ea

    Expected Output:

    REGEXP_REPLACE('A,CA,VA,EA','A','X')
    ------------------------------------
    X,ca,X,X
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    SQL> SELECT regexp_replace('a,ca,va,ea','(\s?a)','X') FROM dual;

    REGEXP_REP
    ----------
    X,cX,vX,eX

    SQL>
     
    Vicky likes this.
  6. Vicky

    Vicky Forum Advisor

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

    But its replacing only first 'a', but not if one also @ middle or last..:(

    Code (SQL):
    SELECT regexp_replace('a,ca,va,a,ea,A,a','^a','X') FROM dual;
    Current Output:

    REGEXP_REPLACE('A,CA,VA,A,EA,A,A','^A','X')
    -------------------------------------------
    X,ca,va,a,ea,A,a

    Expected Output:

    REGEXP_REPLACE('A,CA,VA,A,EA,A,A','^A','X')
    -------------------------------------------
    X,ca,va,X,ea,A,X
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    it is necessary to make correctly an expression template .
    provide an example of a line of data
     
  8. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Sergy,
    I just want to replace the whole 'a' from the input(in all locations..), except in combination..

    For eg:

    Code (SQL):
    SELECT regexp_replace('a,ca,va,a,ea,A,a','^a','X') FROM dual;
    Here, 'a' in all places have to be replaced with 'X'..except in combinations..
     
    Last edited: Sep 23, 2015
  9. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi David,
    I want to replace only single 'a' whole character, but not the 1 in combinations..
     
  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    this example without regexp
    Code (SQL):
    SELECT ltrim(rtrim(REPLACE (','||'a,ca,va,ea,a,123,a'||',',',a,',',X,'),','),',') exm FROM dual;
    for example (the decision on the basis of the regular expressions for this task not the best option)
    Code (SQL):

    SELECT
    listagg(decode(chars,'a','X',chars),',') WITHIN GROUP (ORDER BY rn) char_str
    FROM(
    SELECT rownum rn,regexp_substr('a,ca,va,a,b,a,ea','[^,]+',1,level) chars
    FROM dual
    CONNECT BY regexp_substr('a,ca,va,a,b,a,ea','[^,]+',1,level) IS NOT NULL);
     
     
    Last edited: Sep 23, 2015
    Vicky likes this.
  11. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Tnx Sergy, but my oracle version is Oracle Database 11g Enterprise Edition Release 11.1.0.6.0.. Cud U help me in achieving the same in pre Oracle 11.2 versions...
     
  12. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Vicky, this solution

    Code (SQL):
    SELECT ltrim(rtrim(REPLACE (','||'a,ca,va,ea,a,123,a'||',',',a,',',X,'),','),',') exm FROM dual;
    is worked on oracle version above 10.x.x.x ))
     
    Last edited: Sep 24, 2015
  13. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes, Sergy the above solution works, but I told about the one using LISTAGG(bcz i dont wanna concatenate ',' in code)..

    Also, how can we achieve the same for multiple characters..,

    SELECT regexp_replace('a,ca,va,ea,r,y,q,b,g','(^a)(^y)(^q)','X') result FROM dual;

    Expected Output:

    result
    -------------------------------------------
    'X,ca,va,ea,r,X,X,b,g
     
  14. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hi frnds..
    Any Clue regarding this requirement without going for a procedure or function..,?!
     
  15. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    you only sql-the decision is necessary?
     
  16. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Yes Sergy, Actually, I found successfully how to replace multiple characters..But it's also disturbing those in combinations ..

    Code (SQL):
    SELECT regexp_replace('a,ca,va,ea,r,y,q,b,g','(a|y|q|g)','X') RESULT FROM dual;
    Current Output:

    RESULT
    --------------------
    X,cX,vX,eX,r,X,X,b,X


    Expected Output:

    result
    ------------------------
    'X,ca,va,ea,r,X,X,b,X
     
    Last edited: Sep 24, 2015