Most Efficient Method For Adding Leading 0's To An Int In Sql

I need to return two fields from a database concatenated as 'field1-field2'. The second field is an int, but needs to be returned as a fixed length of 5 with leading 0's. The met

Solution 1:

That is pretty much the way: Adding Leading Zeros To Integer Values

So, to save following the link, the query looks like this, where #Numbers is the table and Num is the column:

SELECTRIGHT('000000000'+CONVERT(VARCHAR(8),Num), 8) FROM #Numbers

for negative or positive values

declare@vvarchar(6)
select@v=-5SELECTcasewhen@v<0then'-'else''end+RIGHT('00000'+ replace(@v,'-',''), 5) 

Solution 2:

Another way (without CAST or CONVERT):

SELECTRIGHT(REPLACE(STR(@NUM),' ','0'),5)

Solution 3:

If you can afford/want to have a function in your database you could use something like:

CREATEFUNCTIONLEFTPAD
           (@SourceString VARCHAR(MAX),
            @FinalLength  INT,
            @PadChar      CHAR(1)) 
RETURNSVARCHAR(MAX)
ASBEGINRETURN
    (SELECT Replicate(@PadChar, @FinalLength - Len(@SourceString)) + @SourceString)
END

Solution 4:

I would do it like this.

SELECTRIGHT(REPLICATE('0', 5) +CAST(Field2 ASVARCHAR(5),5) 

Not necessarily all that "Easier", or more efficient, but better to read. Could be optimized to remove the need for "RIGHT"

Solution 5:

If you want to get a consistent number of total strings in the final result by adding different number of zeros, here is a little bit modification (for vsql)

SELECT 
  CONCAT(
    REPEAT('0', 9-length(TO_CHAR(var1))), 
    CAST(var1 AS VARCHAR(9))
  ) as var1

You can replace 9 by any number for your need!

BRD