Want to Update Record in Table RRS feed

  • Question

  • User-1615062673 posted



    In Oracle Table there is one columns in this columns there is this types of data




    means E:\Elink\spancopowerdocs\NSC\ is same in all row.

    so i want to remove E:\Elink\spancopowerdocs\NSC\ in all rows.



    thanx in adv.

    Friday, September 16, 2011 2:23 AM


  • User699558600 posted

    write a clean string function as shown below

    SQL> create or replace function clean_str(in_string in varchar2)
    2 return varchar2
    3 is
    4 out_string varchar2(4000);
    5 remove_char varchar2(100);
    6 begin
    7 remove_char:='E:\Elink\spancopowerdocs\';
    8 select
    9 REPLACE(TRANSLATE(in_string,remove_char , ' '),' ',NULL)
    10 into out_string
    11 FROM dual;
    12 return (out_string);
    13 end;
    14 /


    Once function is created write a query like this to check whether you get correct data

    select clean_str(a) from yourTableName;

    once you are saisfied do this

    Update yourTableName Set columnName = clean_str(a);

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 16, 2011 2:54 AM