Update column with leading zeros

Question

Hope someone can help. I am trying to pad a zero infront of any result that has under 10 digits in column GPF4. The column GPF4 is designed to be Numeric(18,0) as later on I will be converting this number into Hexidecimal with reversed pairs. result comes with no changes

here is the code to try and pad the zero infront

UPDATE Filter_All
SET GPF4 = RIGHT(CONVERT(varchar(10), REPLICATE('0', 10 - LEN(GPF4)) + CONVERT(varchar(10), GPF4)) + CONVERT(varchar(10), GPF4), 10)
WHERE LEN(GPF4) < 10

• actual number 821295578 in GPF4

• expected result 0821295578

• Resulted with 821295578 (no changes)

I tired again, this time sucessfull howerever the result was padded with the last digit from the of the affected row in GPF4

UPDATE Filter_All
SET GPF4 = RIGHT(CONVERT(varchar(10), REPLICATE('0', 10 - LEN(GPF4)) + CONVERT(varchar(10), GPF4)) + CONVERT(varchar(10), GPF4), 10)
WHERE LEN(GPF4) < 10

• actual number 821295578 in GPF4
• expected result 0821295578
• Resulted with 8821295578

here is the code to try and pad the zero infront

UPDATE Filter_All
SET GPF4 = RIGHT(CONVERT(varchar(10), REPLICATE('0', 10 - LEN(GPF4)) + CONVERT(varchar(10), GPF4)) + CONVERT(varchar(10), GPF4), 10)
WHERE LEN(GPF4) < 10

• actual number 821295578 in GPF4
• expected result 0821295578
• Resulted with 821295578 (no changes)

I tired again, this time sucessfull howerever the result was padded with the last digit from the of the affected row in GPF4

UPDATE Filter_All
SET GPF4 = RIGHT(CONVERT(varchar(10), REPLICATE('0', 10 - LEN(GPF4)) + CONVERT(varchar(10), GPF4)) + CONVERT(varchar(10), GPF4), 10)
WHERE LEN(GPF4) < 10

• actual number 821295578 in GPF4
• expected result 0821295578
• Resulted with 8821295578

Answer 1

Try using concatenation as follows:

SELECT GPF4
    ,CONCAT(REPLICATE('0',10 - LEN(GPF4)),GPF4)
FROM table_name
--WHERE LEN(GPF4)<10

The above example provides an output with GPF4 and padded GPF4

You can try same logic in update statement.

Answer 2

To zero-pad, you'll have to use a string datatype. Using the STR function with a size is a fast way to make a numeric type into a right-justified string. Then you can use the replace function to change spaces to zeroes, which gives you the zero padding.

If, however, your number is larger than 10 digits, you'll just get *** as a result. So, if you have access to IIF, you can check if the number has fewer than the minimum characters before trying to zero pad it.

Here's an example with a numeric(18,0) datatype converted to a zero-padded string based off a parameter for the minimum number of characters in the string:

declare @t table (
   GPF4 numeric(18,0)
);
insert @t (GPF4)
values (821295578)
     , (1234567890123456)
     , (5)
     , (54321)
declare @MinStringSize int = 10;

select GPF4
     , replace(str(GPF4,10),' ','0') as NearlyRight
     , iif(len(cast(GPF4 as varchar(18)))<@MinStringSize,replace(str(GPF4,@MinStringSize),' ','0'),cast(GPF4 as varchar(18))) as ZeroPaddedString
from @t;

Answer 3

Replicated the question. And while concatenating, I tried converting both leading 0's and gpf4 into varchar. And it worked.

https://dbfiddle.uk/0TlMaC_s

Please try and let me know if this works.