update query

[pras]

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

[Shestakov]

Probably u have to write some function for a data conversion.
Example:

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);

[jmodic]

This quite simple update SQL statement will also do:

UPDATE ur_table
SET ur_field = LPAD(ur_field, 4, '0')
WHERE TRANSLATE(ur_field, 'x0123456789', 'x') IS NULL;

[pras]

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

[ccastaneda]

Originally posted by jmodic
This quite simple update SQL statement will also do:

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.. :D


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;

[pras]

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

[jmodic]

Originally posted by ccastaneda

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:

UPDATE ur_table
SET ur_field = LPAD(ur_field, GREATEST(LENGTH(ur_filed),4), '0')
WHERE TRANSLATE(ur_field, 'x0123456789', 'x') IS NULL;

[stecal]

Point, jmodic.