How to convert int to char with leading zeros ?

Question

I need to convert int data table field to char leading zeros

example:

1 convert to '001'

867 convert to '000867'

thx.

---

( This is my response 4 Hours later ... )

I tested this T-SQL Script and work fine for me !

DECLARE @number1 INT, @number2 INT

SET @number1 = 1

SET @number2 = 867

SELECT RIGHT('000' + CAST(@number1 AS NCHAR(3)), 3 ) AS NUMBER_CONVERTED

SELECT RIGHT('000000' + CAST(@number2 AS NCHAR(6)), 6 ) AS NUMBER_CONVERTED

---

I created this user function

T-SQL Code :

CREATE FUNCTION CIntToChar

(

@intVal Int,

@intLen Int

)

RETURNS nvarchar(24)

AS

BEGIN

IF @intlen > 24
SET @intlen = 24

RETURN REPLICATE('0',@intLen-LEN(RTRIM(CONVERT(nvarchar(24),@intVal)))) 
    + CONVERT(nvarchar(24),@intVal)

END

Example

SELECT dbo.CIntToChar( 867, 6 ) AS COD_ID

OUTPUT

000867

Answer 1

I found a good article here:

Padding a string with leading zeros

I've need to do this a lot of times when outputing a fixed length data export where for instance a numeric column is 9 characters long and you need to prefix with leading 0's.

Answer 2

Use REPLICATE so you don't have to hard code all the leading zeros:

DECLARE @InputStr int
       ,@Size     int
SELECT @InputStr=123
      ,@Size=10

PRINT REPLICATE('0',@Size-LEN(RTRIM(CONVERT(varchar(8000),@InputStr)))) + CONVERT(varchar(8000),@InputStr)

OUTPUT:

0000000123