-
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.
-
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);
-
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?
-
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.
-
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;
-
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.
-
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?
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|