PadLeft function in T-SQL

Solution 1:

I believe this may be what your looking for:

SELECT padded_id = REPLACE(STR(id, 4), SPACE(1), '0') 

FROM tableA

SELECT REPLACE(STR(id, 4), SPACE(1), '0') AS [padded_id]

FROM tableA

I haven't tested the syntax on the 2nd example. I'm not sure if that works 100% - it may require some tweaking - but it conveys the general idea of how to obtain your desired output.

EDIT

To address concerns listed in the comments...

@pkr298 - Yes STR does only work on numbers... The OP's field is an ID... hence number only.

@Desolator - Of course that won't work... the First parameter is 6 characters long. You can do something like:

SELECT REPLACE(STR(id,
(SELECT LEN(MAX(id)) + 4 FROM tableA)), SPACE(1), '0') AS [padded_id] FROM tableA

this should theoretically move the goal posts... as the number gets bigger it should ALWAYS work.... regardless if its 1 or 123456789...

So if your max value is 123456... you would see 0000123456 and if your min value is 1 you would see 0000000001

Solution 2:

SQL Server now supports the FORMAT function starting from version 2012, so:

SELECT FORMAT(id, '0000') FROM TableA

will do the trick.

If your id or column is in a varchar and represents a number you convert first:

SELECT FORMAT(CONVERT(INT,id), '0000') FROM TableA

Solution 3:

declare @T table(id int)
insert into @T values
(1),
(2),
(12),
(123),
(1234)

select right('0000'+convert(varchar(4), id), 4)
from @T

Result

Solution 4:

Old post, but maybe this helps someone out:

To complete until it ends up with 4 non-blank characters:

SELECT RIGHT ('0000'+COLUMNNAME, 4) FROM TABLENAME;

To complete until 10:

SELECT RIGHT ('0000000000'+COLUMNNAME, 10) FROM TABLENAME;

In case the column is numeric, convert it to varchar first with such code:

Select RIGHT('0000'+Convert(nvarchar(20), COLUMNNAME), 4)
From TABLENAME

And to complete until 10 with a numeric field:

SELECT RIGHT ('0000000000'+Convert(nvarchar(20), COLUMNNAME), 10) FROM TABLENAME;

Solution 5:

Try this:

SELECT RIGHT(REPLICATE('0',4)+CAST(Id AS VARCHAR(4)),4) FROM [Table A]