Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to convert int to char with leading zeros?

People also ask

Can an int have leading zeros?

You can add leading zeros to an integer by using the "D" standard numeric format string with a precision specifier. You can add leading zeros to both integer and floating-point numbers by using a custom numeric format string. This article shows how to use both methods to pad a number with leading zeros.

How do I preserve the leading zeros when I insert a number into this table?

If you say insert 023 into an int-type col, the engine will compress the number by stripping the 0, since the number is defined as an int. So, basically, if you want leading zeroes, then use , eg, decimal(8,4) & insert .

How do I add a zero in front of a number in Oracle?

When using Oracle Database to format a number to have leading zeros, we need to convert it to a string and format it accordingly. You can use the TO_CHAR(number) function to format numbers with leading zeros.


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

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


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')

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

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.