SQL Server Padding 0's with RIGHT Not Working

Question

I am using SQL Server 2008.

I have a varchar(30) column which holds check numbers. If the payment was made using a credit card, checknbr will be a zero-length string. I need to pad this data with leading zeroes such that the entire output is ten digits long. If there is no check number, it should just be ten zeroes.

Here is the data I would like to see:

    0000000000
    0000000000
    0000000114
    0000000105
    0000000007

Here is the query I have been using and am stuck on:

    SELECT RIGHT((CASE  
        WHEN LEN(checknbr) = 0 THEN '0000000000'
        ELSE '0000000000'+checknbr
    END),10) as checknbr
    FROM payhistory
    WHERE number = 12345678

And this is what I get:

    0000000000
    0000000000
    114
    105
    7

Trying another way:

    SELECT RIGHT('0000000000'+checknbr,10)
    FROM payhistory
    WHERE number = 3861821

And I get the same results. Oddly enough, I have a varchar(10) column where

    RIGHT('0000'+EDC.DatabaseNumber,4) 

yields exactly the results I am after. What am I missing here? Something about a quirk with the RIGHT function?

Thanks in advance

Answer 1

If you are using SQL server 2012 or up try the following:

SELECT RIGHT(CONCAT('0000000000', checknbr), 10)

Concat will automatically convert NULL values to empty strings, removing the need for extra checks.

By the way, the issue you're having is the fact that your query still sees checknbr as a number instead of a varchar value. Casting it to varchar will solve that for you.

EDIT SQL Server 2008:

Since you have a VARCHAR column with no NULL values the only thing that still comes to mind are trailing spaces. Try:

SELECT RIGHT('0000000000' + RTRIM(checknbr), 10);