Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Location
    Delhi, India
    Posts
    15

    Unanswered: Fix SSN field leading zero

    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,

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 06-07-10 at 08:34.

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    SSN is 9 digits number.

    So, we have to use:

    Right(DIGITS(old_ssn), 9) AS new_ssn

    Lenny

  4. #4
    Join Date
    Nov 2008
    Location
    Delhi, India
    Posts
    15
    Quote Originally Posted by tonkuma View Post
    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???

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 06-09-10 at 06:16. Reason: Add "Teste on Database server = DB2/NT 9.7.2"

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •