-2

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
neotrojan
  • 69
  • 7
  • 5
    Would be much easier: `SELECT RIGHT('0000000000' + CODBADGE, 10)` .... – marc_s Nov 22 '21 at 16:34
  • 1
    N.B. If `CODBADGE` is an INT (which from your results it appears it is) then you'd need to use `RIGHT(CONCAT('0000000000', CODBADGE), 10)` instead, otherwise `0000000000` will be implicitly converted to a number, and SQL Server will do addition rather than concatenation. You could also use `FORMAT(x.CODBADGE, '0000000000')`, but [`FORMAT` does not scale particularly well](https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but) – GarethD Nov 22 '21 at 16:39
  • 3
    @GarethD: correct - in that case, use `SELECT RIGHT('0000000000' + CAST(CODBADGE AS VARCHAR(10)), 10)` – marc_s Nov 22 '21 at 16:41
  • OK thank you. solved with: SELECT RIGHT ('0000000000' + CAST (CODBADGE AS VARCHAR (10)), 10) – neotrojan Nov 22 '21 at 16:44

3 Answers3

0

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
Ketan Kotak
  • 942
  • 10
  • 18
0

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
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
-2

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

Daniel GAUDRY
  • 1
  • 4
    What is `ada.strings.fixed."0"` ? That's not a SQL Server system table or function – Panagiotis Kanavos Nov 22 '21 at 16:45
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 22 '21 at 17:24