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

Your code is correct. Where are you seeing the non-padded values? — Gordon Linoff, Aug 25 '15 at 02:29

Felix Pamittan · Answer 1 · 2015-08-25T02:37:46.773

2

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;

edited Aug 25 '15 at 02:37

answered Aug 25 '15 at 02:26

Felix Pamittan

if i do that, I am getting this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name_id), 3) from tblCoordinates' at line 1 – user3201441 Aug 25 '15 at 02:27
Try using `CAST` instead. – Felix Pamittan Aug 25 '15 at 02:28
do i still need to convert the name_id to varchar even if the data type is varchar already? – user3201441 Aug 25 '15 at 02:28
1

@user3201441 No, you don't have to convert anything. Concatenating string using `CONCAT(...)` – Lukasz Szozda Aug 25 '15 at 02:42

Lukasz Szozda · Answer 2 · 2015-08-25T02:44:13.390

0

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

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

edited Aug 25 '15 at 02:44

answered Aug 25 '15 at 02:28

Lukasz Szozda

having an error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NVARCHAR(10)),3)' at line 1. Again I am using 2005 version of MySql – user3201441 Aug 25 '15 at 02:31
You use MySQL and you tag question as sql-server. – Lukasz Szozda Aug 25 '15 at 02:33
@user3201441 Give data example and look at this http://sqlfiddle.com/#!2/9eecb7db/22 – Lukasz Szozda Aug 25 '15 at 03:36

2 Answers2