update and substr command

hi guys,

could someone help me here?

i am try to update a field in the database. the field looks something like this

PONUMBER
po 6666
po8765
PO7876
PO 7654

basically i want remove the po part. I know i have to the substr and updates commands, but i dont know how to impliment the two together. so the field should look like this

PONUMBER
6666
8776
7876
7654

thanks in advance.

Douglas

well, to do it in an update statement you would need to write a function to strip out the PO's. are all the numbers 4 digits? if that's the case then your work will be pretty easy, something like


create or replace procedure remove_po (
field in varchar2,
)
return varchar2
as
begin
return substr(field, -4);
end;


then you would update the database with


update table set ponumber = remove_po(po_number);


on the other hand, if your numbers are not always 4, then you can't rely on that type of procedure, you would need some extra logic, i think this would work

if there is a space
return all of string past the space;
else if there is an 'o' (upper or lower)
return all of the string past the 'o'.

that would translate into something like


create or replace procedure remove_po (
field in varchar2
) return varchar2
as
i number;
begin
i := instr(field, ' ');
if (i is not null) then -- i think null is returned when not there, double check
return substr(field, i+1);
else
i := instr(lower(field), 'o');
if (i is not null) then
return substr(field, i+1);
else
return field;
end if;
end if;
end;


that could be cleaned up and optimized a bit but if you just want a quick fix that should work, but note that i did that off the top of my head so obviously run it on a test table first to make sure (after you get it to compile, and understand what it's doing).

if there is no space, or no 'o', then that procedure will just return the original value. if there are/can be different variations of the fields (eg, more than one space between the PO and the number, trailing whitespace, etc, you will have to modify it to fit your rules)

UPDATE table_name
SET po_number=replace(upper(po_number),'PO','');

should do what you want. Alternatively :

UPDATE table_name
SET po_number=substr(po_number,3);

Test the things first.

right that's a bit easier as long as you don't need a lot of logic to figure out what to strip off :) looked like he could have different variations, eg space, no space, etc.

It can be done in 1 update:
update tablename set PONUMBER = to_number(to_char(ltrim(substr(PONUMBER,3,8))));

Assumption the column length is 10.