Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Exclamation Unanswered: Replacing a substring

    Hi,

    I need to replace a substring in a column with another string for all the rows returned by a query.

    I thought I may have to do it using a cusror. Is there any simpler way to do the same?

    I am new to PL/SQL,so does anybody give me some sample code that does this string manipulation and also using cursors.

    Thanks in advance
    Sateesh.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I need to replace a substring in a column with another string for all the rows returned by a query.
    Replace it where? In the table column itself? Only in the displayed output?
    If both the source and target are fixed (unchanging), you "should" be able to accomplish this with vanilla SQL. Pure SQL runs much faster than PL/SQL.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Replacing a substring

    Use REPLACE:

    SQL> select ename, replace(ename, 'IN', 'XY')
    2 from emp;

    ENAME REPLACE(ENAME,'IN','
    ---------- --------------------
    SMITH SMITH
    ALLEN ALLEN
    WARD WARD
    JONES JONES
    MARTIN MARTXY
    BLAKE BLAKE
    CLARK CLARK
    SCOTT SCOTT
    KING KXYG
    TURNER TURNER
    ADAMS ADAMS
    JAMES JAMES
    FORD FORD
    MILLER MILLER

  4. #4
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Re: Replacing a substring

    Tony,

    Thanks. That works fine. Does it update the column in the table or just it outputs the updated string. I am looking for updating the column itself.

    Sateesh.



    Originally posted by andrewst
    Use REPLACE:

    SQL> select ename, replace(ename, 'IN', 'XY')
    2 from emp;

    ENAME REPLACE(ENAME,'IN','
    ---------- --------------------
    SMITH SMITH
    ALLEN ALLEN
    WARD WARD
    JONES JONES
    MARTIN MARTXY
    BLAKE BLAKE
    CLARK CLARK
    SCOTT SCOTT
    KING KXYG
    TURNER TURNER
    ADAMS ADAMS
    JAMES JAMES
    FORD FORD
    MILLER MILLER

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Replacing a substring

    If you want to update the table you need an UPDATE statement:

    update emp
    set ename = replace( ename, 'IN', 'XY');

  6. #6
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Re: Replacing a substring

    Thanks a lot. It worked fine.


    Originally posted by andrewst
    If you want to update the table you need an UPDATE statement:

    update emp
    set ename = replace( ename, 'IN', 'XY');

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •