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

What do you want to return if the number = 0 or the number is < 0??? — Jeff Moden, Jan 21 '23 at 01:31

score 2 · Answer 1 · answered Jan 19 '23 at 20:59

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

score 0 · Answer 2 · answered Jan 19 '23 at 20:30

0

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)

answered Jan 19 '23 at 20:30

Hogan

69,564
10
76
117

2

This is a quote from the requirements : `Desired result: 12340000` looks like the zeros are at the end to me @XavierJ – Hogan Jan 19 '23 at 20:47
1

@XavierJ -- even the title of the question says trailing zeros -- Why do you think this is wrong? – Hogan Jan 19 '23 at 20:51
Whoops, I misread that – Xavier J Jan 19 '23 at 21:09

score 0 · Answer 3 · answered Jan 19 '23 at 21:06

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)).

score -2 · Answer 4 · edited Jan 19 '23 at 21:07

-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

edited Jan 19 '23 at 21:07

Dale K

25,246
15
42
71

answered Jan 19 '23 at 20:47

Rafa N

1
1

2

This won't work since 1234567 is not a varchar constant. How exactly is this different than the answer I posted 20 mins ago? – Hogan Jan 19 '23 at 20:48
1

It isn't I just did not see your answer, so I decided to post the solution myself. – Rafa N Jan 19 '23 at 21:07

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

4 Answers4