T-SQL how to add trailing zeros to a string varchar

Question

My number needs to be 8 digits long, however If its less than 8 digits long I need to add trailing zeros to the it.

• Example: 1234
• Desired result: 12340000

I tried this at first:

DECLARE @YourNumber VARCHAR(8)=1234567;
SELECT DISTINCT 
LEFT('00000000'+CAST(ISNULL(@YourNumber,0) AS VARCHAR),8)  

However the result is: 00000000

Answer 1

If what you are asking for is actually what you want then try this:

DECLARE @YourNumber VARCHAR(8)='1234567';
SELECT DISTINCT 
LEFT(CAST(ISNULL(@YourNumber,0) AS VARCHAR)+'00000000',8)  

Answer 2

All you have to do is move the "+ '00000000'" portion of the code to the right side of the Cast Function.

DECLARE @YourNumber VARCHAR(8)=1234567;
SELECT DISTINCT 
LEFT(CAST(ISNULL(@YourNumber,0) AS VARCHAR)+'00000000',8)

Resulting in final value of: 12345670

Answer 3

I have the same read as @Hogan +1. I just tend to opt for concat(). No need to test for nulls or even care if the value is a string or int

Example

Select IfInt = left(concat(1234  ,'00000000'),8)
      ,IfStr = left(concat('1234','00000000'),8)
      ,IfNull= left(concat(null  ,'00000000'),8) 

Results

IfInt       IfStr       IfNull
12340000    12340000    00000000

Answer 4

Since you are starting with a "number" in a string (DECLARE @YourNumber VARCHAR(8)=1234567;) there is no need to use cast. You can simply add the required number of zeroes:

DECLARE @YourNumber VARCHAR(8)= '1234567'; -- Using a string rather than an   Int  literal.
select @YourNumber + Replicate( '0', 8 - Len( @YourNumber ) ) as PaddedString;

Aside: It is a best practice to always specify the length of strings, i.e. CAST(ISNULL(@YourNumber,0) AS VARCHAR(8)).