PadLeft function in T-SQL

Question

I have the following table A:

id ---- 1 2 12 123 1234 

I need to left-pad the id values with zero's:

id ---- 0001 0002 0012 0123 1234 

How can I achieve this?

Answer 1

I believe this may be what your looking for:

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

or

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

Answer 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