views:

68

answers:

2

In SQL Server 2008, I want to represent an integer as a 3-character string - so:

  • 3 becomes '003'
  • 5 becomes '005'
  • 107 becomes '107'

How can I do this?

+7  A: 
/* Method 1 Using RIGHT function */
SELECT RIGHT('000' + CAST(NumericColumn AS VARCHAR(3)), 3) PaddedCnumericColumn 
FROM MyTable


/* Method 2 Using RIGHT AND REPLICATE function */
SELECT  RIGHT(REPLICATE('0', 3) + CAST(NumericColumn AS VARCHAR(3)), 3) PaddedCnumericColumn 
FROM MyTable
Mitch Wheat
The first approach here is pretty cute.
Marc Gravell
A: 

You can try this

DECLARE @Table TABLE(
     Val INT
)

INSERT INTO @Table (Val) SELECT 1
INSERT INTO @Table (Val) SELECT 10
INSERT INTO @Table (Val) SELECT 100

SELECT  REPLICATE('0',3 - LEN(CAST(Val AS VARCHAR(3)))) + CAST(Val AS VARCHAR(3))
FROM    @Table
WHERE   ABS(Val) < 1000
astander