We help IT Professionals succeed at work.

PL/SQL to copy a number row to a varchar2 row w/ some formatting?

KGNickl
KGNickl asked
on
I have a table with the below fields. The VERSION_TEXT field is a new field I added. I had numbers such a 1.0 that would get turned into 1. So I added an extra field which will actually output the numbers. The new code supports both fields, but the existing data does not, so I need a PL/SQL script to populate the empty VERSION_TEXT row the same night the new code goes live.

ID (Number) which is the Primary Key
VERSION (Number) - Existing and has values
VERSION_TEXT (VarChar2) - New field w/ no values currently

Logic is as follows:

For each row in the table{
    If the version value is a whole number{
        update the empty version_text field to be the whole number followed by .0 (so 1 in version would be 1.0
        in version_text)
    }else{
        Just copy the value from the version field to the version_text field ( 0.01 would get copied from version to
       version_text as 0.01).
    }
}

I figured I could just develope a java, perl, vb script. But if PL/SQL could handle this I would rather go that way and run the script directly against the DB. Not sure if this is something someone could write out in 2 minutes off the top of there mind? Provide me direction (don't have much PL/SQL experience)? Or if I'm better just doing it in another language? I figure the decimal part would be what might not be able to be done w/ PL/SQL, but I would think the rest wouldn't be too difficult? Thanks!
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
u pdate yourtable set version_text = to_char(version,'fm9999999.09')

no if/then needed.

just make sure the 9999999 part is big enough to support whatever numbers you might need to convert
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
case when mod(versionValue,1) = 0 then to_char(versionValue)||'.0' else to_char(versionValue) end
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
if you need more decimal places supported just add more 9's, but keep the first 0


to_char(version,'fm9999999999999999.099999999999')
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
also, rather than creating a new column that must be maintained, you might want to consider using a view that displays the string version as when needed

create or replace view my_view as
select id, version, to_char(version,'fm9999999999999999.099999999999') version_text
from your_table

Explore More ContentExplore courses, solutions, and other research materials related to this topic.