title: How to convert int to char with leading zeros?
body: <p>I need to convert int datafield to nvarchar with leading zeros</p>
  
  <p>example:</p>
  
  <p>1 convert to '001'</p>
  
  <p>867 convert to '000867', etc.</p>
  
  <p>thx.</p>
  
  <hr>
  
  <p>This is my response 4 Hours later ...</p>
  
  <p>I tested this T-SQL Script and work fine for me !</p>
  
  <pre><code>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
  </code></pre>
  
  <hr>
  
  <p>I created this user function</p>
  
  <p>T-SQL Code :</p>
  
  <pre><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 &gt; 20
         SET @intlen = 20
  
      RETURN REPLICATE('0',@intLen-LEN(RTRIM(CONVERT(nvarchar(20),@intVal)))) 
          + CONVERT(nvarchar(20),@intVal)
  
  END
  </code></pre>
  
  <p>Example :</p>
  
  <p>SELECT dbo.CIntToChar(  867,  6  ) AS COD_ID</p>
  
  <p>OUTPUT</p>
  
  <p>000867</p>