sql server - How to convert int to char with leading zeros?

Question

I need to convert int datafield to nvarchar with leading zeros

example:

1 convert to '001'

867 convert to '000867', etc.

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 BIGINT, @intLen Int) RETURNS nvarchar(20)
AS
BEGIN

    -- BIGINT = 2^63-1 (9,223,372,036,854,775,807) Max size number

    -- @intlen contains the string size to return
    IF @intlen > 20
       SET @intlen = 20

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

END

Example :

SELECT dbo.CIntToChar( 867, 6 ) AS COD_ID

OUTPUT

000867

Answer 1

Try this: select right('00000' + cast(Your_Field as varchar(5)), 5)

It will get the result in 5 digits, ex: 00001,...., 01234