postgresqlnetezza

Adding leading zero if length is not equal to 10 digit using sql

I am trying to join 2 tables but my problem is that one of the table has 10 digit number and the other one may have 10 or less digit number. For this reason, i am loosing some data so i would like to do is check the length first if the length is less than 10 digit then i want to add leading zeros so i can make it 10 digit number. I want to do this when i am joining this so i am not sure if this is possible. Here is an example if i i have 251458 in the TABLE_WITHOUT_LEADING_ZERO then i want to change it like this: 0000251458. Here is what i have so far:

select ACCT_NUM, H.CODE
 FROM TABLE_WITH_LEEDING_ZERO D,  TABLE_WITHOUT_LEADING_ZERO H
 WHERE substring(D.ACCT_NUM from position('.' in D.ACCT_NUM) + 2) = cast (H.CODE as varchar (10))

thanks

Solution

  • In Netezza you can use LPAD:

    select lpad(s.sample,10,0) as result
from (select 12345 as sample) s

    result
   -------
  0000012345

    However it would be more efficient to remove the zeros like in the example below:

    select cast(trim(Leading '0' from s.sample) as integer) as result
from (select '0000012345' as sample) s

    result
   -------
    12345