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

Question

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

Snowden

As i do not know how to do this in postgresql, i deleted my answer. – Orianna

Use something like select lpad('12345', 10, '0000000000') – Houseman

The 10 zero's in the third parameter can be reduced to 1: select lpad('12345', 10, '0'); – Witkin

Answer 1

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

Boustrophedon

Answer 2

Another alternative:

SELECT TO_CHAR(12345,'fm0000000000');
  to_char   
------------
 0000012345

Sousaphone

But note that lpad() usually also work on string containing non-digits while to_char() work only when converting from number types to string. – Raving

Answer 3

You can achieve using LPAD

SELECT LPAD('7', 2, '0');

Output : "07"

Reforest