SQL Server Padding 0's with RIGHT Not Working -

i using sql server 2008.

i have varchar(30) column holds check numbers. if payment made using credit card, checknbr zero-length string. need pad data leading zeroes such entire output ten digits long. if there no check number, should ten zeroes.

here data see:

    0000000000     0000000000     0000000114     0000000105     0000000007 

here query have been using , stuck on:

    select right((case           when len(checknbr) = 0 '0000000000'         else '0000000000'+checknbr     end),10) checknbr     payhistory     number = 12345678 

and get:

    0000000000     0000000000     114     105     7 

trying way:

    select right('0000000000'+checknbr,10)     payhistory     number = 3861821 

and same results. oddly enough, have varchar(10) column

    right('0000'+edc.databasenumber,4)  

yields results after. missing here? quirk right function?

thanks in advance

if using sql server 2012 or try following:

select right(concat('0000000000', checknbr), 10) 

concat automatically convert null values empty strings, removing need checks.

by way, issue you're having fact query still sees checknbr number instead of varchar value. casting varchar solve you.

edit sql server 2008:

since have varchar column no null values thing still comes mind trailing spaces. try:

select right('0000000000' + rtrim(checknbr), 10);