DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: update query

  1. #1
    Join Date
    Jul 2000
    Location
    india
    Posts
    213

    update query

    Hi Everybody,

    I have a table having a column as varchar2.The column has at present data of many rows.

    The data stored in the column are for e.g..

    10
    2508
    NU-CMA13
    1
    45
    ....
    ....

    Now i want to update the column data like wherever there are numeric data like 10 it needs to be updated as 4 digit(padded with 2 leading zeroes) so the data should look like...

    0010
    2508
    NU-CMA13
    0001
    0045
    ....
    ....

    Pls if anybody kknows the solution...Thanks in advance

    pras
    Dream as if u'll live forever..Live as if u'll die today.

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Probably u have to write some function for a data conversion.
    Example:
    Code:
    create or replace
    function convert_data (p_value varchar2) return varchar2 is
      n number;
    begin
      n := to_number(p_value);
      return to_char(n,'0999');
    exception 
      when invalid_number then  return p_value;
      when others then raise_application_error(-20100, 'Undefine error.');
    end;
    /
    
    update ur_table set ur_field = convert_data(ur_field);

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    This quite simple update SQL statement will also do:
    Code:
    UPDATE ur_table
      SET ur_field = LPAD(ur_field, 4, '0')
      WHERE TRANSLATE(ur_field, 'x0123456789', 'x') IS NULL;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Jul 2000
    Location
    india
    Posts
    213

    Thanks

    Hi jmodic and shestakov,

    Thank you very much for the solutions.I am going with the jmodic's solution since i simply want to update the table.

    Pras
    Dream as if u'll live forever..Live as if u'll die today.

  5. #5
    Join Date
    Sep 2001
    Posts
    37
    Originally posted by jmodic
    This quite simple update SQL statement will also do:
    Code:
    UPDATE ur_table
      SET ur_field = LPAD(ur_field, 4, '0')
      WHERE TRANSLATE(ur_field, 'x0123456789', 'x') IS NULL;
    Jmodisc solution will work fine.. Just don't forget that lpad will truncate all records that have length greater than 4 (in this case)..

    A little modification to Jmodics' query will prevent this..

    Code:
    UPDATE ur_table
      SET ur_field = 
      DECODE( SIGN(4-LENGTH(ur_field)), 1, LPAD(ur_field,4,'0'), ur_field )
      WHERE TRANSLATE(ur_field, 'x0123456789', 'x') IS NULL;

  6. #6
    Join Date
    Jul 2000
    Location
    india
    Posts
    213
    Hi ccastaneda,

    Yeah ur correct..but the numeric data in my varchar2 field is not more than the 4 digit.for e.g if it is 1234 then it will remain as 1234 .only alphanumeric data is more than 4 digit which will remain as it is.Anyway thank you very much for the generalised query..

    pras
    Dream as if u'll live forever..Live as if u'll die today.

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by ccastaneda
    Code:
    UPDATE ur_table
      SET ur_field = 
      DECODE( SIGN(4-LENGTH(ur_field)), 1, LPAD(ur_field,4,'0'), ur_field )
      WHERE TRANSLATE(ur_field, 'x0123456789', 'x') IS NULL;
    Good catch for making this update as general as possible. However, while we are at tthat, I would change the above command further a bit, just to make it more readable and also a bit more efficient:
    Code:
    UPDATE ur_table
      SET ur_field = LPAD(ur_field, GREATEST(LENGTH(ur_filed),4), '0')
      WHERE TRANSLATE(ur_field, 'x0123456789', 'x') IS NULL;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    May 2002
    Posts
    2,645
    Point, jmodic.

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