Formatting Numbers by Padding With Leading Zeros in SQL Server

Formatting Numbers by padding with leading zeros in SQL Server

Change the number 6 to whatever your total length needs to be:

SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId

If the column is an INT, you can use RTRIM to implicitly convert it to a VARCHAR

SELECT REPLICATE('0',6-LEN(RTRIM(EmployeeId))) + RTRIM(EmployeeId)

And the code to remove these 0s and get back the 'real' number:

SELECT RIGHT(EmployeeId,(LEN(EmployeeId) - PATINDEX('%[^0]%',EmployeeId)) + 1)

Concatenation Breaks Numbers with Padded Zeros in SQL Server

I think this does what you want:

convert(varchar(6), a.[Fiscal Year] * 100 + a.[Fiscal Month])

Query to pad left of a field with 0's

Assuming that MuNumber is VARCHAR simply use RIGHT

SELECT RIGHT('00000' + MuNumber, 5)
FROM Mytable

Otherwise you need to convert it first

SELECT RIGHT('00000' + CONVERT(VARCHAR(5), MuNumber), 5)
FROM Mytable

And in general you can use this pattern:

DECLARE @num INT = 10;

SELECT RIGHT(REPLICATE('0', @num) + CONVERT(VARCHAR(5), MuNumber), @num)
FROM Mytable

How to add zero in front of numeric value in T-SQL 2005 or 2008?

You have to convert it to a varchar and use right.

select right(replicate('0', 10) + convert(varchar(10), YourNumberColumn), 10)

Left fill variable with zeros in FORMATMESSAGE

Try %0{# of digits}i (%02i) like so:

Declare @i int = 2

PRINT FORMATMESSAGE('La La La: %02i', @i)

-- OUTPUT: 'La La La: 02'

Your string:

PRINT FORMATMESSAGE('Run time #%02i: From %s to %s', @i, CONVERT(VARCHAR(10), @from_date, 101), CONVERT(VARCHAR(10), @to_date, 101))

SQL Server ID Starting with Zero's

This is no datatype that will pad leading 0s. That type of thing must be done with a character datatype. But you can accomplish this easily.

Also please be sure to always specify the length of varchar data. https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length

create table MCE_IDTest_av(
invoice_id_num int not null Identity(100076,1) primary key,
invoice_id as ('MCE' + right('00000000' + CONVERT(varchar(10), invoice_id_num), 7)),

);

---

Related Topics