We help IT Professionals succeed at work.
Get Started

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

KGNickl asked
Last Modified: 2013-12-18
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)
        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!
Watch Question
Database Developer & Administrator
Most Valuable Expert 2011
Top Expert 2012
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE