Fix SSN field leading zero

nitingautam · #1 (**permalink**) 06-07-10, 07:17

Hi,

We have a Table in which SSN was specified as INTEGER hence it was removing the leading zeros of SSN ID.
For example 001234567 is getting saved as 1234567.

To resolve this issue, now we are converting the field type to varchar, this will fix issue for all new records but how to fix issue for existing records.

Means, I want to add the leading zeros as specified in below example to fix old values:

OLD SSN (in integer field)= 123456
NEW SSN (in varchar field)= 000123456

OLD SSN=1234567
NEW SSN=001234567

Thanks,

tonkuma · #2 (**permalink**) 06-07-10, 07:30

Code:

------------------------------ Commands Entered ------------------------------
SELECT old_ssn
     , DIGITS( old_ssn ) AS new_ssn
  FROM (VALUES INTEGER(123456) ,INTEGER(1234567) ) AS q(old_ssn);
------------------------------------------------------------------------------

OLD_SSN     NEW_SSN   
----------- ----------
     123456 0000123456
    1234567 0001234567

  2 record(s) selected.

Lenny77 · #3 (**permalink**) 06-07-10, 09:59

SSN is 9 digits number.

So, we have to use:

Right(DIGITS(old_ssn), 9) AS new_ssn

Lenny

nitingautam · #4 (**permalink**) 06-09-10, 03:24

Quote:

Originally Posted by tonkuma

Code:

------------------------------ Commands Entered ------------------------------
SELECT old_ssn
     , DIGITS( old_ssn ) AS new_ssn
  FROM (VALUES INTEGER(123456) ,INTEGER(1234567) ) AS q(old_ssn);
------------------------------------------------------------------------------

OLD_SSN     NEW_SSN   
----------- ----------
     123456 0000123456
    1234567 0001234567

  2 record(s) selected.

tonkuma in the above example you used a virtual data. means not exist in table, what we say this concept???

If I want to create the same above thing but with two values means in pair , how to do it???

tonkuma · #5 (**permalink**) 06-09-10, 05:10

Quote:

tonkuma in the above example you used a virtual data. means not exist in table, what we say this concept???

If I want to create the same above thing but with two values means in pair , how to do it???

Although, I couldn't understand well second sentence,
I hope that following example may give you some ideas.

Tested on
Database server = DB2/NT 9.7.2

CREATE TABLE and INSERT data:

Code:

------------------------------ Commands Entered ------------------------------
CREATE TABLE nitingautam.t1
( ssn_id INTEGER
);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
INSERT INTO nitingautam.t1
VALUES 123456;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM nitingautam.t1;
------------------------------------------------------------------------------

SSN_ID     
-----------
     123456

  1 record(s) selected.

ALTER and REORG TABLE:

Code:

------------------------------ Commands Entered ------------------------------
ALTER TABLE nitingautam.t1
ALTER COLUMN ssn_id
  SET DATA TYPE VARCHAR(9);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
REORG TABLE nitingautam.t1;
------------------------------------------------------------------------------
DB20000I  The REORG command completed successfully.

INSERT new data:

Code:

------------------------------ Commands Entered ------------------------------
INSERT INTO nitingautam.t1
VALUES '001234567';
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM nitingautam.t1;
------------------------------------------------------------------------------

SSN_ID   
---------
123456   
001234567

  2 record(s) selected.

UPDATE old data:

Code:

------------------------------ Commands Entered ------------------------------
UPDATE nitingautam.t1
   SET ssn_id = RIGHT('00000000' || ssn_id , 9)
 WHERE LENGTH( RTRIM(ssn_id) ) < 9
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM nitingautam.t1;
------------------------------------------------------------------------------

SSN_ID   
---------
000123456
001234567

  2 record(s) selected.

Internet Services

Web Development

Digital Marketing

Consumer Tech