Mass update of data in sql from int to varchar

Question

We have a large table (5608782 rows and growing) that has 3 columns Zip1,Zip2, distance

All columns are currently int, we would like to convert this table to use varchars for international usage but need to do a mass import into the new table convert zip < 5 digits to 0 padded varchars 123 becomes 00123 etc. is there a way to do this short of looping over each row and doing the translation programmaticly?

Answer 1

for SQL Server....

try this, assumes nvarchar(20) for the new column:

INSERT INTO NewTable
       (col1, col2, col3
       ,NewZip)
    SELECT
        col1, col2, col3
        ,RIGHT(REPLICATE(N'0',20)+CONVERT(nvarchar(20),oldZip),20) --null will result in null
        FROM OldTable

Answer 2

The following example is assuming you are using MS SQL Server. See here for help on the replicate function.

insert  NewZipTable
        (Zip1,
        Zip2,
        Distance)
select  replicate('0', 5 - len(Zip1)) + CAST(Zip1 as VARCHAR(10)),
        replicate('0', 5 - len(Zip2)) + CAST(Zip2 as VARCHAR(10)),
        Distance
from    OldZipTable

Answer 3

Don't do such things in a loop, they can usually be done using pure SQL with much better performance.

Also, always store zip-codes and other things that can't be used for calculations as characters. Never heard of a 10% zip-code increase :)

---

Example using Oracle:

Insert Into new_table (zip1, zip2, distance)
    Select
        TO_CHAR( zip1, 'FM00000' ),
        TO_CHAR( zip2, 'FM00000' ),
        distance
    From old_table

Answer 4

In MySQL you could do this:

INSERT INTO newTable (zip1, zip2, distance) 
SELECT LPAD(zip1, 5, '0'), LPAD(zip2, 5, '0'), distance
FROM oldTable;

Answer 5

A simple RIGHT will do it in SQL Server

INSERT
    newTable (...
SELECT
    RIGHT('00000' + CAST(Zip AS varchar(5)), 5),
    ...
FROM
    OldTable