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  gt  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

User · Answer

You can also use FORMAT   function introduced in SQL Server 2012  http   technet microsoft com library hh213505 aspx  DECLARE  number1 INT   number2 INT  SET  number1   1 SET  number2   867  SELECT FORMAT  number1   d10   SELECT FORMAT  number2   d10

User · Answer

Not very elegant  but I add a set value with the same number of leading zeroes I desire to the numeric I want to convert  and use RIGHT function   Example   SELECT RIGHT CONVERT CHAR 7  1000000    number2  6       Result   000867

User · Answer

This work for me in MYSQL   FUNCTION leadingZero format VARCHAR 255   num VARCHAR 255     RETURNS varchar 255  CHARSET utf8 BEGIN   return CONCAT SUBSTRING format 1 LENGTH format -LENGTH num   num   END   For example   leadingZero  000  999   returns  999  leadingZero  0000  999   returns  0999  leadingZero  xxxx  999   returns  x999    Hope this will help  Best regards

User · Answer

Data type  int  cannot be more than 10 digits  additionally the  Len    function works on ints  so there s no need to convert the int to a string before calling the len   function   Lastly  you are not taking into account the case where the size of your int   the total number of digits you want it padded to   intLen    Therefore  a more concise   correct solution is   CREATE FUNCTION rf f CIntToChar  intVal Int   intLen Int  RETURNS nvarchar 10  AS BEGIN     IF  intlen  gt  20 SET  intlen   20     IF  intlen  lt  LEN  intVal  RETURN RIGHT CONVERT nvarchar 10    intVal    intlen      RETURN REPLICATE  0    intLen - LEN  intVal     CONVERT nvarchar 10    intVal  END

User · Answer

Had same issue  this is how I resolved    Simple and elegant  The  4  is how long the string should be  no matter what length of integer is passed it will pad with zero s up to  4    STUFF SomeVariableOrField 1 0 REPLICATE  0  4 - LEN SomeVariableOrField

User · Answer

In my case I wanted my field to have leading 0 s  in 10 character field  NVARCHAR 10    The source file does not have the leading 0 s needed to then join to in another table  Did this simply due to being on SQL Server 2008R2   Set Field   right   0000000000     Field   10    Can t use Format   as this is pre SQL2012   Performed this against the existing data   So this way 1 or 987654321 will still fill all 10 spaces with leading 0 s   As the new data is being imported  amp  then dumped to the table through an Access database  I am able to use Format  Field   0000000000   when appending from Access to the SQL server table for any new records

User · Answer

I like to use   DECLARE  Length int DECLARE  Number int SET  Length   9 SET  Number   4  select right  POWER 10   Length     Number   Length    this gives me   000000004

User · Answer

Works in SQLServer      declare  myNumber int   123 declare  leadingChar varchar 1     0  declare  numberOfLeadingChars int   5  select right REPLICATE    leadingChar    numberOfLeadingChars     cast  myNumber as varchar max     numberOfLeadingChars    Enjoy

User · Answer

DECLARE  number1 INT   number2 INT  SET  number1   1  SET  number2   867   -- Without the  RTRIM   the value returned is 3        SELECT RIGHT  000    RTRIM CAST  number1 AS NCHAR 3    3    AS NUMBER CONVERTED   -- Without the  RTRIM   the value returned is 867           SELECT RIGHT  000000    RTRIM CAST  number2 AS NCHAR 6    6    AS NUMBER CONVERTED

User · Answer

One line solution  per se  for SQL Server 2008 or above   DECLARE  DesiredLenght INT   20  SELECT      CONCAT          REPLICATE               0                 DesiredLenght-LEN  Column       1 SIGN  DesiredLenght-LEN  Column      2              Column   FROM Table    Multiplication by SIGN expression is equivalent to MAX 0   DesiredLenght-LEN  Column     The problem is that MAX   accepts only one argument

User · Answer

Try this  select right  00000    cast Your Field as varchar 5    5   It will get the result in 5 digits  ex  00001       01234

User · Answer

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

User · Answer

Using RIGHT is a good option but you can also do the following   SUBSTRING CAST  POWER 10  N    value  AS NVARCHAR N   1    2  N    where N is the total number of digits you want displayed   So for a 3-digit value with leading zeros  N   3    SUBSTRING CAST  POWER 10  3    value  AS NVARCHAR 4    2  3    or alternately  SUBSTRING CAST 1000   value  AS NVARCHAR 4    2  3    What this is doing is adding the desired value to a power of 10 large enough to generate enough leading zeros  then chopping off the leading 1   The advantage of this technique is that the result will always be the same length  allowing the use of SUBSTRING instead of RIGHT  which is not available in some query languages  like HQL

User · Answer

Not to High-Jack this question but the note needs to be made that you need to use  N VARCHAR instead of  N CHAR data-type   RIGHT  000    CAST  number1 AS NCHAR 3    3     Above segment  will not produce the correct response from SQL 2005  RIGHT  000    CAST  number1 AS NVARCHAR 3    3     Above segment  will produce the desired response from SQL 2005  The the varchar will provide you with the desired prefix length on the fly  Where as the fixed length data type will require length designation and adjustments

User · Answer

I was wondering  would this be on any use to you   declare  val int  len 800 select replace str  val  len       0

User · Answer

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

User · Answer

select right  000    convert varchar 3  id  3  from table      example     declare  i int     select  i  1  select right  000    convert varchar 3   i  3    BTW if it is an int column then it will still not keep the zeros Just do it in the presentation layer or if you really need to in the SELECT

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

Examples related to sql-server

Examples related to stored-procedures