CODEWITHSUNDEEP
sqlsql-server
Mike Bennett
Mike Bennett

Reputation: 345

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 method i'm using is:

SELECT Field1 + '-' + RIGHT('0000' + CAST(Field2 AS varchar),5) FROM ...

Is there a more efficient way to do this?

Upvotes: 16

Views: 72655

Answers (5)

user2662753
user2662753

Reputation: 41

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

Upvotes: 0

DJ.
DJ.

Reputation: 16257

Another way (without CAST or CONVERT):

SELECT RIGHT(REPLACE(STR(@NUM),' ','0'),5)

Upvotes: 9

SQLMenace
SQLMenace

Reputation: 135121

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:

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

for negative or positive values

declare @v varchar(6)
select @v = -5

SELECT case  when @v < 0 
then '-' else '' end + RIGHT('00000' + replace(@v,'-',''), 5)

Upvotes: 40

Petros
Petros

Reputation: 8992

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

CREATE FUNCTION LEFTPAD
           (@SourceString VARCHAR(MAX),
            @FinalLength  INT,
            @PadChar      CHAR(1)) 
RETURNS VARCHAR(MAX)
AS
BEGIN
  RETURN
    (SELECT Replicate(@PadChar, @FinalLength - Len(@SourceString)) + @SourceString)
END

Upvotes: 2

Mitchel Sellers
Mitchel Sellers

Reputation: 63136

I would do it like this.

SELECT RIGHT(REPLICATE('0', 5) + CAST(Field2 AS VARCHAR(5),5)

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

Upvotes: 1

Related Questions