I am trying to join two numbers, but as a result I need a string. If I add two numbers, the zeros are removed. I need a 10-character string

select
    case 
        when Len(CODBADGE) = 1 then '000000000'  CODBADGE
        when Len(CODBADGE) = 2 then '00000000'  CODBADGE
        when Len(CODBADGE) = 3 then '0000000'  CODBADGE
        when Len(CODBADGE) = 4 then '000000'  CODBADGE
        when Len(CODBADGE) = 5 then '00000'  CODBADGE
        when Len(CODBADGE) = 6 then '0000'  CODBADGE
        when Len(CODBADGE) = 7 then '000'  CODBADGE
        when Len(CODBADGE) = 8 then '00'  CODBADGE
        when Len(CODBADGE) = 9 then '0'  CODBADGE
        when Len(CODBADGE) = 10 then CODBADGE
        else NULL
    end as code10
from
    TAB_BADGE

CODBADGE

1 
10
100
1000
10000
100000
1000000
10000000
100000000
1000000000

result :

0000000001 
0000000010
0000000100
0000001000
0000010000
0000100000
0001000000
0010000000
0100000000
1000000000

CodePudding user response：

You can use replicate function

SELECT left( replicate( '0', 10 ), 10 - len( CODBADGE) )   cast(CODBADGEas varchar(10)) as code10 FROM TAB_BADGE

or you can use right function

SELECT  RIGHT('0000000000'   CAST(CODBADGE AS VARCHAR(10)), 10) as code10 FROM TAB_BADGE

CodePudding user response：

Just another option is format().

To be clear... due to performance issues it should be used sparingly.

select code10 = format(CODBADGE,'0000000000')
 From  TAB_BADGE

CodePudding user response：

you could use ada.strings.fixed."0" (Len(CODBADGE)," ") Len(CODBADGE) one line instead of many no case no nothing