Pad Varchar with 0 in SQL Server 2005

Question

I have tried following this and this as I need to pad a column with zeros.

So I have a field

name_id
1
2
21
74

And I want it to be like

name_id
001
002
021
074

So I have tried doing this:

SELECT RIGHT('000'+ name_id,3) from tblCoordinates;

But the result is:

RIGHT('000'+name_id,3)
1
2
21
74

I am using MySQL Server 2005. What is wrong with select statement? Thanks

Answer 1

You need to convert name_id to VARCHAR first:

SELECT RIGHT('000' + CONVERT(VARCHAR(3), name_id), 3) from tblCoordinates;

If you're using MySQL, there is a built-in function LPAD()

SELECT LPAD(name_id, 3, '0') from tblCoordinates;

Answer 2

SELECT '000' + '1' => 1 For concatenation use CONCAT()

SELECT RIGHT(CONCAT('000',name_id),3)
FROM tblCoordinates;