modify column value

[phm]

hi,

I need to modify one column values for all rows of the table from a value containing hiphens to without that.

Like : 123-21-6345 to 123216345 .

[badrinathn]

You could use the replace function to do this: follow the egs:

create table temp (numb varchar2(20));
insert into temp values ('123-21-6345');
update temp set numb= replace('123-21-6345 ','-','');

Hope this helped
badrinath

[phm]

Thanks Badrinath.
But this column is a primary key and this has to be done
for all the records of the table and not for a particular string.
Is it possible ?

[badrinathn]

Hi,
I should be possible. You can write a procedure/plsql block.Take the columns in the cursor.Process those records one by one and change their values in the PK.However before doing it make sure you have it changed on the dependent tables.

For changing it in the child tables, you could do it in this way
1) Make sure, nobody is using the tables.Disable the foreign key constraint and run the procedure seperately on both the parent table and child table and re enable the references.

badrinath

[phm]

yes, but does replace function takes a variable as a input ?

[badrinathn]

I think It works,
begin
declare
val varchar2(10) := '123-34-33';
begin
val := replace(val,'-','');
dbms_output.put_line(val);
end;
end;
/

SQL> /
1233433

you could just try in a procedure, and let me know
badrinath

[phm]

yes, it worked.
I could do it simply like this without writing a procedure.
update t1
set col1 =replace(col1,'-',' ');

Thanks a lot.