update and substr command
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: update and substr command

  1. #1
    Join Date
    Sep 2000
    Posts
    23

    Unhappy

    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

  2. #2
    Join Date
    Jun 2000
    Posts
    417
    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

    <font face="courier">
    create or replace procedure remove_po (
    **field in varchar2,
    )
    return varchar2
    as
    begin
    **return substr(field, -4);
    end;
    </font>

    then you would update the database with

    <font face="courier">
    update table set ponumber = remove_po(po_number);
    </font>

    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

    <font face="courier">
    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;
    </font>

    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)

  3. #3
    Join Date
    Jul 2000
    Posts
    521
    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.
    svk

  4. #4
    Join Date
    Jun 2000
    Posts
    417
    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.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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.

Posting Permissions

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



Click Here to Expand Forum to Full Width